Friday 11 November 2011

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

   


******IMPORTANT NOTE******
Only now after a few years of comments, I understand the big confusion I generated, and I understand it's been my fault and for that I do apologize.
The query works but NOT in MSSMS: the query works in ASP or in a Ms Access environment with linked tables to MS SQL. I hope this clarify a bit...


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.

46 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
  11. Hi,
    it doesn't work on SQL 2016 and Windows 10.
    I replicated the 2 tables and executed the exact query of the example.
    Result: Incorrect syntax near the keyword 'INNER'
    What a pity, it would have been useful to me.
    Bye

    ReplyDelete
    Replies
    1. Again read the post! The answer is in there. The code working but you need to be careful on relations.

      Delete
  12. Thanks for replying immediately, Marco.
    I premise that I could talk Italian, but I go on with English to let everybody understand.

    As I said, to avoid as much problems as possible, and test the query, I created the 2 tables on my DB; same names, fields ID as INT, fields name and address as VARCHAR. Same content. Same query.

    I'm using Management Studio to access database, to exclude problems from other software.
    The result is always:

    Message 156, level 15, state 1, line 4
    Incorrect syntax near the keyword 'INNER'.

    I add that i'm using EXPRESS Edition (Advanced) of SQL 2016; I don't think that makes any difference, tough.

    Should be considered, instead, that every site i've seen says that it is not possible to update two tables in one single query.
    You have to use 2 queries: in a transaction, in a stored procedure... as you like, but in 2 queries.
    I would be glad to prove otherwise... :)

    The problem seems to be the system not accepting "UPDATE names" followed by "INNER" (not even "LEFT" or any other form of a JOIN, anyway).

    How are You using this query to make it work?
    I mean, I read that You used it many times but, if You try it now, which system do use?
    Which version of SQL? Which program to access database and try the query?

    Thank You,
    David

    ReplyDelete
    Replies
    1. I have the same environment as you (SSMS express and so on).
      The query is working but the relations must be done correctly. The error near INNER is recurring but is not related apparently to anything else than relations between the IDs.
      I am sorry but I cannot help anymore.

      Delete
    2. Try to use aliases for table names. That's the only thing I can think of.

      Delete
  13. First of all, many thanks for Your time.
    It's been greatly appreciated.

    Regarding the problem, i must only add that, following your suggestion, I noticed that SSMS, in the statement:

    UPDATE names INNER JOIN addresses ON names.ID = addresses.ID ...

    recognises the 1st "names" as a table (You can see the related hint if You put the cursor on it), but NOT the 1st "addresses".
    It could be caused by not accepting INNER in that position (the syntax error, most likely), or by "addresses" in itself...
    I will continue trying to manipulate it. If I'm successful, I'll come back and write it here.

    It's not so great a problem, however; i can accomplish my tasks in other ways...
    Let's say that I like when everybody says "You cannot" and You find a way to do it anyway!

    Thanks again.
    David

    ReplyDelete
    Replies
    1. No problem. As said why don't you try with aliases as in
      UPDATE names as b INNER JOIN

      Delete
  14. I tried at once, as You suggested, but it doesn't work.
    I only obtain that the error becomes: Incorrect syntax near the keyword 'as'.
    It works only if I update just 1 field and add "FROM table", like in:

    UPDATE names
    SET names.name = 'Peter' FROM names
    INNER JOIN adresses ON names.ID = adresses.ID
    WHERE names.ID = 1

    If I try to update a 2nd field, in any way, the error comes back.

    I'd give up quietly, if I didn't know that it worked for you... :)
    I'll be in touch.

    Thanks and bye
    David

    ReplyDelete
  15. Note that Oracle DB doesn't support this syntax. On Oracle you would have to do two separate updates.

    ReplyDelete
    Replies
    1. Thanks Klas, good to know, but I'm trying to do it on Microsoft SQL.
      Every once in a while I go back and take a look at this;
      at the moment I'm stuck with foreign keys and temporary tables but, with a single query, I still haven't found a working method.
      I do not give up, though! :)
      Bye

      Delete
  16. update aibOrderInfo INNER JOIN aiborderdetails ON aibOrderInfo.OrderID=aiborderdetails.OrderID set aibOrderInfo=391 ,aiborderdetails.OrderID=391 where orderId=6817

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

    ReplyDelete
  17. Tired of reading the whole comments? Always the same syntax error which is avoidable by reading the article and the comments.
    I won't authorize any more comments on this post. Sorry.

    PS: I wonder why recently web app are so terrible... :-)

    ReplyDelete
    Replies
    1. The error is with inner as it returns only matching columns between two tables. U can normally use just join or full outer join to avoid the same

      Delete
    2. The error is with inner syntax as it returns only matching column. You can use full outer join instead

      Delete
  18. In over 5 years, with people telling you it doesn't work, you arrogantly slap everyone down and insist you are correct. What I notice is that you have never stated what database you are using. I bet it is not Microsoft SQL Server, because the syntax you advocate is quite simply illegal in all versions. It is NOT possible to update multiple tables using a single statement in SQL Server.

    ReplyDelete
    Replies
    1. Ok. You’re right. I am telling a lot of lies.
      Oh by the way I used SQL Server Express Edition.

      Delete
  19. It's good that you have finally admitted your mistake Marco. It will save people wasting a lot of time, trying to reproduce something that cannot be reproduced (except by you).

    ReplyDelete
  20. As a final statement I did test it as of now and it works, but now maybe I understand the big confusion, and maybe it's my fault and for that I do apologize.
    The query works but NOT in MSSMS: the query works in ASP or in a Ms Access environment with linked tables to MS SQL.

    I hope this clarify a bit...

    ReplyDelete
  21. May I ask whats the use of WHERE in the statement? Thank you in advance

    ReplyDelete
  22. May I ask whats the use of WHERE in the statement? What should i write there? Thank you in advance

    ReplyDelete
  23. It works Marco

    ReplyDelete
  24. UPDATE
    employee d
    INNER JOIN
    employees g
    ON
    d.ID = g.empno
    set
    d.first_name ='Jasonww', g.ename = 'Jasonwrrr'
    and
    d.ID = '1' and g.empno='1'


    when i try to run the above query face "missing set keyword"
    please rectify this error

    ReplyDelete

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

However, I do answer to all the comments.