Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
UPDATE with a JOIN |
Sun, Oct 19 2014 11:32 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Mon, Oct 20 2014 4:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Oct 20 2014 5:15 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 > > > |
Tue, Oct 21 2014 2:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |