Friday, 11 November 2011

SQL: how to update two (or more) tables at the same time

   


Sometimes, when searching for an answer, we end up making things too much complicated, while easy solutions are just round the corner. This is the case of a simple task like updating two related tables with just one SQL query.

Suppose we have two related tables. The first contains user names, and the second email addresses related to the first table names.

First table ("names")
IDname
1John
2James

The second table ("addresses")
IDaddress
1First Street
2Second Street

How do we change the name and the street of the first record (with id equal to 1)?
With one simple query.


The query
When we need to update one table, we usually perform a single SQL query like:
UPDATE
names
SET
name = 'Peter'
WHERE
ID = 1
Simple as that.
But if we want to update the two tables at the same time?
We can use a JOIN based on the ID field.
UPDATE
names
INNER JOIN
addresses
ON
names.ID = addresses.ID
SET
names.name = 'Peter', addresses.address = 'Third Street'
WHERE
names.ID = 1
The advantages?
Well, there is an immediate advantage in performing just a single SQL query instead of two, and I believe it is quite clear: the server will have a lighter work load.
At the same time, we will have a full control on the performed operation, which will be faster and easier to maintain.

Sometimes, solutions are easier than thought, and I believe this is the case.

22 comments:

  1. Thanx a lot man

    ReplyDelete
    Replies
    1. It is not working on SQL 2005 i tried to update and it is giving error on inner join

      Update A Inner Join B on A.ID=B.ID Set
      Set A.Status=2,
      B.Taken= B.Taken + A.Days
      Where A.Status=1

      Delete
  2. I'm trying to do this in SQL Developer and I keep getting an error message on "Missing SET keyword":

    UPDATE donor
    LEFT JOIN relation
    ON donor.donrid = relation.relid
    SET donor.donrlrel = 'N',
    relation.relisa = 'fp',
    relation.relwhose = 'fpm',
    relation..relstopdat = '01-APR-2013'
    WHERE r.relrelatid in ('0000515049', '0000505999')
    AND r.relisa = 'p'
    AND r.relwhose = 'pm'

    Any ideas?

    ReplyDelete
    Replies
    1. Two dots here
      relation..relstopdat = '01-APR-2013'

      Delete
    2. Sorry I deleted your comment by mistake. Same error?
      Be careful with dates. Is the date a date field? If so can't set the value like that.
      Other thing is the join: why left and not inner?

      Delete
    3. I ended up building to separate updates instead and it worked.
      Thanks anyway.

      Delete
    4. Believe me it has to work. Please read carefully the post. Be sure to use the same ids and so on.
      Thanks.

      Delete
  3. BEGIN
    UPDATE FS_ACTIVITY_VERSION AV
    INNER JOIN FS_ACTIVITY A
    ON
    AV.FS_ACTIVITY_ID = A.FS_ACTIVITY_ID
    SET
    AV.FS_ACTIVITY_VERSION_ID=:P524_FS_ACTIVITY_VERSION_ID,
    AV.FS_ACTIVITY_ID=:P524_FS_ACTIVITY_ID,
    AV.FS_VERSION_ID=:P524_FS_VERSION_ID,
    AV.DL_ALERT_MAIL=:P524_FS_VERSION_ID,
    AV.MIN_ACTIVITY_PER_FS=:P524_MIN_ACTIVITY_PER_FS,
    AV.ACTIVITY_SIZE_GB=:P524_ACTIVITY_SIZE_GB,
    A.FS_ACTIVITY_ID=:P524_FS_ACTIVITY_ID,
    A.FS_ACTIVITY_NAME=:P524_FS_ACTIVITY_NAME
    WHERE
    FS_ACTIVITY_VERSION_ID=:P524_ID;
    END;


    1 error has occurred
    ORA-06550: line 3, column 32: PL/SQL: ORA-00971: missing SET keyword ORA-06550: line 3, column 1: PL/SQL: SQL Statement ignored

    ReplyDelete
    Replies
    1. Sure about this:
      UPDATE FS_ACTIVITY_VERSION AV
      INNER JOIN FS_ACTIVITY A

      Tables name looks different.

      In any case, it's very difficult to pinpoint errors with such complex queries. How can I help people when I can't replicate errors?
      What's that ORA... field? Is it mandatory?
      Can't help you there. Sorry.

      Delete
  4. what if we have to update three tables ? what will be the query for that ?

    ReplyDelete
  5. It is not working

    UPDATE
    dbo.t1 INNER JOIN dbo.t2
    on t1.id=t2.id
    set t1.title='LU1',t2.MRP='1001'
    WHERE
    t1.id=1

    Error : Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'INNER'.

    ReplyDelete
    Replies
    1. I'm quite fed up when I read "it is not working". Everywhere.
      I have used the above code many times and it is working.

      When I see things like the above I wonder if it's fake, advertising or what, because a decent coder won't write something like that, hoping that I could help him/her sort out the syntax error. I don't know if t1 and t2 are tables, What kind of relation, if any, there is between the two, what tools it's been used or if you had cereal for breakfast this morning or something else (and if possible, I don't really care)... and then in the end I'm sorry but I read arrogance up there.

      Now, get back to your code and think about it. And in any case remove those dbo. from it.

      Delete
  6. Hey i am too facing the same problem .

    UPDATE
    person
    INNER JOIN
    pdetail
    ON
    person.ID = pdetail.ID
    SET
    person.name = 'sanket', pdetail.phone = '9004110919'
    WHERE
    e1.ID = 1

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'INNER'.

    ReplyDelete
    Replies
    1. You read what I write, don't you?
      What is e1.ID = 1?

      Delete
  7. This is not working.
    Update Tab1
    Join Tab2 on Tab1.ID = Tab2.ID
    set IncludeList = 0, office = 'Piraeus'
    where tab1.Matter = 1234

    I am running this on SQL 2008 R2 and I get and error
    Incorrect syntax near the keyword 'Join'

    ReplyDelete
    Replies
    1. Re-read the post. Read it and try to understand. Hint: relation?

      Delete
  8. Hi,

    This is not working I have tried it multiple times with Sql 2005 and 2008 but not working with any environment. Please help me out.

    Thanks

    ReplyDelete
  9. Hi,

    update product.Smartpoint_Customer c
    inner join product.Smartpoint_License l
    on c.id = l.Customer_Id
    set c.name = 'Test', l.License_Key = 'L2'
    where c.id = 2

    I have tried this query with Sql Server 2005 and 2008 but this is not working in any environment. Please help me out.

    Thanks In Advance...

    ReplyDelete
    Replies
    1. As said before, I have used the query a thousand times. It works without any doubt.
      Said that, I cannot help you with the query not knowing the table structure.
      For example, are both ids set as same data type?

      Delete
  10. Hi,
    I also use the same but not working in SQL 2005 and 2008, giving the same error

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'JOIN'.

    Thanks

    ReplyDelete

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.