Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
UPDATE with LEFT JOIN |
Mon, Sep 3 2012 1:57 AM | Permanent Link |
IQA | Hi All,
Any ideas how I can do something like this? Obvioulsly its the LEFT JOIN that's throwing it out, but I need to do a lookup on the company table to check the company.flag field is NULL before allowing it to update. UPDATE reservation SET (companyID, firstname) = (SELECT companyID, firstname FROM reservation WHERE res_num = '1234') LEFT JOIN company ON reservation.companyID = reservation.companyID WHERE company.flag IS NULL AND guestID = 10 AND res_num <> '1234' AND status <= 3! Cheers, Phil |
Mon, Sep 3 2012 3:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Phil
How is it "throwing it out"? Roy Lambert [Team Elevate] |
Mon, Sep 3 2012 4:06 AM | Permanent Link |
IQA | > How is it "throwing it out"?
> > Roy Lambert [Team Elevate] Sorry Roy, poor terminology on my part... I mean throws an error... ElevateDB Error #700 An error was found in the statement at line 7 and column 1 (Expected end of expression but instead found LEFT) On the Query... UPDATE reservation SET (companyID, firstname) = (SELECT companyID, firstname FROM reservation WHERE res_num = '1234') LEFT JOIN company ON reservation.companyID = company.companyID WHERE company.flag IS NULL AND guestID = 22 AND res_num <> '1234' AND status <= 3 |
Mon, Sep 3 2012 4:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Phil
Its a bit difficult for me to see what's going on since you've posted two slightly different versions of the query, however, the first thing that comes to mind is that the parser is getting confused over the tables. Try giving each table instance its own alias so that the parser can figure it out eg UPDATE reservation R SET (companyID, firstname) = (SELECT R2.companyID, R2.firstname FROM reservation R2 WHERE R2.res_num = '1234') LEFT JOIN company C ON R.companyID = C.companyID WHERE C.flag IS NULL AND {no idea}.guestID = 22 AND R.res_num <> '1234' AND {no idea}.status <= 3 If that doesn't work you could try using a sub-select as part of the WHERE clause, or just wait for John Hay to come along. Roy Lambert Roy Lambert [Team Elevate] |
Mon, Sep 3 2012 5:00 AM | Permanent Link |
IQA | > Its a bit difficult for me to see what's going on since you've posted two slightly different versions of the query, however, the first thing that comes to mind is that the parser is getting confused over the tables. Try giving each table instance its own alias so that the parser can figure it out eg
> > UPDATE reservation R > SET (companyID, firstname) = (SELECT R2.companyID, R2.firstname FROM reservation R2 WHERE R2.res_num = '1234') > LEFT JOIN company C ON R.companyID = C.companyID > WHERE C.flag IS NULL AND {no idea}.guestID = 22 AND R.res_num <> '1234' AND {no idea}.status <= 3 > > If that doesn't work you could try using a sub-select as part of the WHERE clause, or just wait for John Hay to come along. > > Roy Lambert Roy Lambert [Team Elevate] Thanks Roy, I'm thinking its the fact I maybe just cant use an UPDATE SET / SELECT statement and have a JOIN on the UPDATE table. But thanks for your help, worse comes to worse I can just use a for / next loop and code it in C++ as its not got to update many records when this takes place. |
Mon, Sep 3 2012 5:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Phil
>Thanks Roy, I'm thinking its the fact I maybe just cant use an UPDATE >SET / SELECT statement and have a JOIN on the UPDATE table. Now you say that its jogged my memory - you can't do it with DELETE anymore and you have to use a subselect. Try UPDATE reservation R SET (companyID, firstname) = (SELECT R2.companyID, R2.firstname FROM reservation R2 WHERE R2.res_num = '1234') WHERE {no idea}.guestID = 22 AND R.res_num <> '1234' AND {no idea}.status <= 3 AND NOT EXISTS (SELECT C.Flag FROM Companies C WHERE R.companyID = C.companyID) It took me ages to get used to the JOIN syntax and then I have to unlearn it Roy Lambert [Team Elevate] |
Mon, Sep 3 2012 6:24 AM | Permanent Link |
IQA | > Now you say that its jogged my memory - you can't do it with DELETE anymore and you have to use a subselect.
> > Try > > UPDATE reservation R > SET (companyID, firstname) = (SELECT R2.companyID, R2.firstname FROM reservation R2 WHERE R2.res_num = '1234') > WHERE > {no idea}.guestID = 22 > AND > R.res_num <> '1234' > AND > {no idea}.status <= 3 > AND > NOT EXISTS (SELECT C.Flag FROM Companies C WHERE R.companyID = C.companyID) > > It took me ages to get used to the JOIN syntax and then I have to unlearn it > > Roy Lambert [Team Elevate] Roy........ You're a legend! That did the trick. I apologise the example wasn't the best. Thanks very much indeed! Phil. |
Wed, Sep 5 2012 8:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Phil,
<< Any ideas how I can do something like this? Obvioulsly its the LEFT JOIN that's throwing it out, but I need to do a lookup on the company table to check the company.flag field is NULL before allowing it to update. >> Your parentheses are off. I'm assuming that you want something like this: UPDATE reservation A SET (companyID, firstname) = (SELECT companyID, firstname FROM reservation B LEFT JOIN company ON company.companyID = B.companyID WHERE B.res_num = A.res_num AND <Other conditions here>) With UPDATE/DELETE statements that need to do joins, just remember that you simply need to "match up" the sub-query with the table being updated/deleted from by using a correlated condition (B.res_num = A.res_num) in the WHERE clause of the sub-query on the primary/unique keys for the outer table. This will ensure that only one row is returned for each row in the outer UPDATE/DELETE, thus giving you a valid result. If you have any other questions, please let me know. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Friday, April 26, 2024 at 06:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |