Hi

In DBISAM v3 I have an UPDATE statement like this:-

----------------------------------------
UPDATE TenantTrans TT SET TT.Period = 1
FROM TenantTrans TT
LEFT OUTER JOIN Tenants T ON T.TenantCode = TT.TenantCode
WHERE TT.Period = 2
   AND TT.TransType = 'Rent Inv'
   AND NOT T.VacateDate IS NULL
----------------------------------------

In ElevateDB it throws an error to say the FROM is not expected.

The manual just says
----------------------------
UPDATE <TableName>
SET <ColumnName> = <Value> [,<ColumnName> = <Value>])
[WHERE <FilterCondition>]
----------------------------
.... and has no helpful example of updating a table with the results of a
JOIN, unlike the DBISAM manual.

Any suggestions on how to go about this?

TIA

Cheers

jeff

Jeff

>In DBISAM v3 I have an UPDATE statement like this:-
>
>----------------------------------------
>UPDATE TenantTrans TT SET TT.Period = 1
>FROM TenantTrans TT
>LEFT OUTER JOIN Tenants T ON T.TenantCode = TT.TenantCode
>WHERE TT.Period = 2
> AND TT.TransType = 'Rent Inv'
> AND NOT T.VacateDate IS NULL
>----------------------------------------

I remember this well. Just after I thought I was becoming acceptable with sql along came the real world and kicked me

What you need to do is use a subselect (which DBISAM didn't support). I think

UPDATE TenantTrans TT SET TT.Period = 1
FROM TenantTrans TT
WHERE TT.Period = 2
AND TT.TransType = 'Rent Inv'
AND NOT EXISTS (SELECT VacateDate FROM Tenants T WHERE T.TenantCode = TT.TenantCode)


should work, but since I'm not an expert and I don't have your data to test don't blame me if your computer goes up in flames

Roy Lambert

Hi Roy

Thanks for that - got me on the right track.

My final (I hope) code looks like this:-

// Moving vacated tenant's Rent Invoices back one period

UPDATE TenantTrans TT SET TT.Period = 1
WHERE TT.Period = 2
AND TT.TransType = 'Rent Inv'
AND EXISTS (SELECT VacateDate
            FROM Tenants T
            WHERE T.TenantCode = TT.TenantCode
              AND NOT T.VacateDate IS NULL)

Nothing burst into flames

Cheers

Jeff


On 20/10/2014 9:46 p.m., Roy Lambert wrote:
> Jeff
>
> I remember this well. Just after I thought I was becoming acceptable with sql along came the real world and kicked me
>
> What you need to do is use a subselect (which DBISAM didn't support). I think
>
> UPDATE TenantTrans TT SET TT.Period = 1
> FROM TenantTrans TT
> WHERE TT.Period = 2
> AND TT.TransType = 'Rent Inv'
> AND NOT EXISTS (SELECT VacateDate FROM Tenants T WHERE T.TenantCode = TT.TenantCode)
>
>
> should work, but since I'm not an expert and I don't have your data to test don't blame me if your computer goes up in flames
>
> Roy Lambert
>
>
>

Jeff


When you get used to it the ability to use subselects is fantastic. Getting JOINs out of your head after bashing them in with a large hammer is not so pleasant

Roy Lambert