Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    India
    Posts
    3

    Lightbulb Unanswered: 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..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Mulitple Update in mYSQL

    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?

  3. #3
    Join Date
    Jul 2002
    Posts
    55
    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?

  4. #4
    Join Date
    Nov 2002
    Location
    India
    Posts
    3

    Thumbs down

    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 ..

  5. #5
    Join Date
    Oct 2003
    Posts
    1

    Re: Mulitple Update in mYSQL

    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

  6. #6
    Join Date
    Jul 2002
    Posts
    55
    No need to shout

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •