Page 1 of 1

query multiple tables, update one?

Posted: Thu Sep 11, 2003 7:24 pm
by Unipus
The server I'm working on is running MySQL 3.23, and I think I may have just run into a problem. Apparently, I'm not allowed the do UPDATE queries that involve multiple tables. The MySQL manual says:
Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
So, I'm guessing this might be the problem. BUT, what I'm doing is not actually a multi-table update, it's just an update of one table using data from two, like so:
UPDATE search, rebates SET search.rebate = '1' WHERE search.ItemNo = rebates.ItemNo
I'd rather not have to upgrade, if possible, because that will probably introduce a whole new can of worms (and differences between development and public servers)... so if there's a way to do this as a single query, please let me know.

Posted: Thu Sep 11, 2003 10:24 pm
by JAM
Dont think that is allowed in mysql v < 4 either...
Does this also generate errors (or perhaps the wrong result)?

Code: Select all

update 
     search 
set 
     search.rebate = '1'
from 
     rebates 
where 
     search.ItemNo = rebates.ItemNo
( Cant test it myself at the time )