UPDATE with JOIN

[Rayn]

I dont know if this can be done but is there a way to do multipe updates in one query?

I know you can select multiple tables using JOIN... but I was always unsure about it.

Thanks

[mattuu]

You can change many rows if they share a common criteria, say:

UPDATE fruit set price=price+.20 where name like "%orange%";

(up the price of anything that has 'orange' in the name by .20)

But that's not what you mean. You can't otherwise.

[Rayn]

Originally posted by mattuu
You can change many rows if they share a common criteria, say:

UPDATE fruit set price=price+.20 where name like "%orange%";

(up the price of anything that has 'orange' in the name by .20)

But that's not what you mean. You can't otherwise.

Getting me off track :p

I figured that wasnt a possibility.. it would be much faster if it exsisted though 🙁

[Mega]

UPDATE table1 t1, table2 t2 SET t1.field='hi', t2.field='mom' WHERE t1.id = t2.id

obviously the WHERE can be altered to anything you please.

I have no idea if this is what you mean but i can try 🙂

[mattr]

This has come up a couple times already. Short answer: For MySQL, No.