#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    41
    Rep Power
    8

    Updating 2 tables in one query when similar data does not exist in both


    I'm trying to update multiple tables in a single query, but what I'm using does not seem to do any updating.

    UPDATE table1,table2 SET table1.name='John Doe',table2.name='John Doe'
    WHERE table1.id=1 and table2.id = 1;


    Problem is, a row with the same id may not be present in both tables. How can I do an update in a case like this?

    In this case, the `id 1` is present in table1, but not in table2.

    The idea is to update data in both tables, even if an id does not exist in table1 or table2

    How do I write the sql for something like this?

    <b>Example:</b>

    id is present in table1 but not in table2 -> Update table1.

    id is present in table2 but not table1 -> update table2.

    id is present in both table1 and table2 update both.

    id is not present in either tables -> do nothing
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    just use two update statements

    why the need for one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo