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

Phil


How is it "throwing it out"?

Roy Lambert [Team Elevate]

> 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

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]

> 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.

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]

> 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.

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