query multiple tables, update one?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

query multiple tables, update one?

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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 )
Post Reply