Can I use a join in an UPDATE query?

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
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Can I use a join in an UPDATE query?

Post by Stryks »

Yeah ... I have a feeling this is a dumb question, but I thought I might as well ask.

The issue is that I have two linked tables which hold a value I want to update, but the value is in table 1 and the ID I have access to is from table 2. Oh yeah, and the ID I have isnt the key used to join these tables.

In order to save a query to match the two tables, I'm kind of wanting to say UPDATE blah WHERE table1.key = table2.key AND table2.ID = 8.

Is this even remotely possible? :?

Thanks
wyred
Forum Commoner
Posts: 86
Joined: Mon Dec 20, 2004 1:59 am
Location: Singapore

Post by wyred »

Have you given it a try?
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Yeah ...

I've had a bit of a go, but I dont really know the format.

Hence the question ... can it be done? And if so, any ideas on how?

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

did you take a look at the documentation for your database?
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Well ... That took some finding, but finally came up with something that seems to be what I'm after.
Starting with MySQL 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;

The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.
Will test it out a little later. I've managed to get over the whole 'rushed and stressed at the end of a long Friday' syndrome, but I'm still enjoying the down-time too much to start tinkering just yet.

Cheers. Will let you know if it doesn't work out. :wink:
Post Reply