Page 1 of 1

Can I use a join in an UPDATE query?

Posted: Fri Feb 18, 2005 1:26 am
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

Posted: Fri Feb 18, 2005 1:36 am
by wyred
Have you given it a try?

Posted: Fri Feb 18, 2005 4:18 pm
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

Posted: Fri Feb 18, 2005 4:19 pm
by feyd
did you take a look at the documentation for your database?

Posted: Fri Feb 18, 2005 10:27 pm
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: