[RESOLVED] UPDATE with JOIN not working

rpupkin77

When I run this script, I get errors:

UPDATE CAC_USERS LEFT JOIN CAC_ORDERS ON CAC_USERS.CAC_USER_ID = CAC_ORDERS.CAC_USER_ID SET CAC_USER.FIRST_NAME = 'asdf' WHERE CAC_ORDERS.ON_OFF = 2;

all names are correct, is my syntax wrong or am I going insane?

NogDog

What DBMS are you using? If MySQL, is it version 4.0.4 or later (the earliest that supports updates using joins)?

rpupkin77

Sorry, MYSQL 5.

NogDog

Seems to me that if you're going to require that CAC_ORDERS.ON_OFF = 2, then you would need to do an inner join instead of a left join; as otherwise any rows in CAC_USERS without a corresponding CAC_USER_ID in CAC_ORDERS would still be eligible for updating, but the where clause would make no sense for them.

rpupkin77

Thanks, Yeah, I tried that with no luck. In fact any syntax (even without using the JOIN keyword) for updates fail. I have no idea what is going on.

I have been trying for a few days, the names are correct, here is the particular error:

1064 - You have an error in your SQL syntax near "INNER JOIN..."

Would there be anything blocking this functionality?

NogDog

Unfortunately, doing an update with a join is uncharted waters for me. Hopefully a real DB guru will come along soon and educate both of us. 🙂

Sxooter

UPDATE CAC_USERS LEFT JOIN CAC_ORDERS ON CAC_USERS.CAC_USER_ID = CAC_ORDERS.CAC_USER_ID SET CAC_USER.FIRST_NAME = 'asdf' WHERE CAC_ORDERS.ON_OFF = 2;

Note that you're saying CAC_USER.FIRSTNAME when the update starts with CAC_USERS ??? Lack of an S maybe?

rpupkin77

Thanks for the help, this is stupid of me, but the box I was testing on was running an older version.

Too many boxes at my job.