Mulitple Update in mYSQL

I need to write a query to update two mysql tables simultaneously ie:
i have two tables:
Table1 and Table2.
and each of the these tables have a realting field... So i tried writing a update query this way.

Update TABLE1,TABLE2 SET TABLE1.field1 = 'aaaa' , TABLE2.field1='bbb' Where TABLE1.field2 = 12 and TABLE1.field1=TABLE2.field1

But this query showed me an error.. i can do this breaking into 2 queries, but i want it to be done in one single go... any idea on this??

Thanking you in advance..Originally posted by nikks525
I need to write a query to update two mysql tables simultaneously ie:
i have two tables:
Table1 and Table2.
and each of the these tables have a realting field... So i tried writing a update query this way.

Update TABLE1,TABLE2 SET TABLE1.field1 = 'aaaa' , TABLE2.field1='bbb' Where TABLE1.field2 = 12 and TABLE1.field1=TABLE2.field1

But this query showed me an error.. i can do this breaking into 2 queries, but i want it to be done in one single go... any idea on this??

Thanking you in advance..
I don't use MySQL but updating 2 tables in one statement is not allowed generally in SQL. One way to achieve something like it (in Oracle at least) is to create a view for the join query with an INSTEAD OF UPDATE trigger. So the user can update one view, and the trigger actually updates 2 tables. I don't know if MySQL supports INSTEAD OF triggers, though.

Why do you want to do it anyway? Is it just a covenience issue or do you have some other reason for not wanting to perform 2 updates?|||I just noticed this before replying to the same thread in the MySQL forum. Simple answer; to the best of my knowledge, you can't. It's not valid SQL. And to extend my learned chum andrewst's comments, MySQL doesn't support triggers or views so no go there I'm afraid.

I'm also intrigued as to why you need to do this?|||I just wanted to do a easy job with writing the update in a single query.. rather than 2 different queries..
yea i think it needs to be broken up into 2 different Queries ..

anyway thanks for your replies ..|||Originally posted by andrewst
I don't use MySQL but updating 2 tables in one statement is not allowed generally in SQL. One way to achieve something like it (in Oracle at least) is to create a view for the join query with an INSTEAD OF UPDATE trigger. So the user can update one view, and the trigger actually updates 2 tables. I don't know if MySQL supports INSTEAD OF triggers, though.

Why do you want to do it anyway? Is it just a covenience issue or do you have some other reason for not wanting to perform 2 updates?

CAN U PLEASE LET ME KNOW HOW TO CREATE VIEWS IN MYSQL TO UPDATE 2 TABLES IN MYSQL|||No need to shout :p

It's been a while since I checked up with developments over at MySQL AB but (see my post above) as far as I know, you can't. No triggers, no updateable views and transaction support only in certain table types.

Why can't you fire off two update statements?

(caveat: I'm quite happy to have my comments above proven wrong by someone more up-to-date on the latest MySQL releases)