How to update two tables data with JOIN in Single query

  • CREATE TABLE ABC (Id INT, Name VARCHAR(10))

    INSERT INTO ABC VALUES (1, 'PK'), (2, 'SK')

    CREATE TABLE ABC1 (Id INT, Name VARCHAR(10))

    INSERT INTO ABC1 VALUES (1, 'KKKKK'), (2, 'MMMMM')

    UPDATE A

    SET

    A.Name = 'PPPPP',

    B.Name = 'PPPPP'

    FROM ABC A JOIN ABC1 B ON A.Id = B.Id

    WHERE A.Id = 1

    On the above query i want to update columns of both table........

  • You need to write two update statements.

  • I want to update in single query, other wise i have to use transaction and i can not use transaction.....

  • You could UPDATE the first table and have an UPDATE TRIGGER on it that UPDATEs the second table.

    But I believe that a transaction is the way to go.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • purushottam2 (8/29/2012)


    I want to update in single query, other wise i have to use transaction and i can not use transaction.....

    Why not? Seems very unreasonable.

    You're using SQL Server 2008 - Google "Composable DML". You can insert into two different tables in the same statement using the OUTPUT from one of them.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (8/29/2012)


    purushottam2 (8/29/2012)


    I want to update in single query, other wise i have to use transaction and i can not use transaction.....

    Why not? Seems very unreasonable.

    You're using SQL Server 2008 - Google "Composable DML". You can insert into two different tables in the same statement using the OUTPUT from one of them.

    "Composable DMS" - so that's the term for it.

    Too bad you can only INSERT the results somewhere or it would be really cool (instead of semi-cool)!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (8/29/2012)


    ChrisM@Work (8/29/2012)


    purushottam2 (8/29/2012)


    I want to update in single query, other wise i have to use transaction and i can not use transaction.....

    Why not? Seems very unreasonable.

    You're using SQL Server 2008 - Google "Composable DML". You can insert into two different tables in the same statement using the OUTPUT from one of them.

    "Composable DMS" - so that's the term for it.

    Too bad you can only INSERT the results somewhere or it would be really cool (instead of semi-cool)!

    Heh good catch mate - thanks!

    USE tempdb

    GO

    DROP TABLE ABC

    CREATE TABLE ABC (Id INT, Name VARCHAR(10))

    INSERT INTO ABC VALUES (1, 'PK'), (2, 'SK')

    DROP TABLE ABC1

    CREATE TABLE ABC1 (Id INT, Name VARCHAR(10))

    INSERT INTO ABC1 VALUES (1, 'KKKKK'), (2, 'MMMMM')

    SELECT * FROM ABC

    SELECT * FROM ABC1

    INSERT ABC1 (Id, Name)

    SELECT *

    FROM (

    UPDATE A SET

    A.Name = 'PPPPP'

    OUTPUT deleted.Id, inserted.Name

    FROM ABC A

    JOIN ABC1 B ON A.Id = B.Id

    WHERE A.Id = 1

    ) d

    SELECT * FROM ABC

    SELECT * FROM ABC1

    You get an error if you try to UPDATE.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Yeah, so now what I haven't tested it with is:

    Since you can use both OUTPUT and OUTPUT INTO within the same INSERT, UPDATE, DELETE or MERGE, can you also then use this OUTPUT as composable DML to INSERT into another table.

    So, one statement gives you:

    INSERT into 3 tables

    DELETE from 1 table, INSERT into 2

    UPDATE into 1, INSERT into 2

    MERGE into 1, INSERT into 2

    ?

    MERGE can also delete at the same time too can't it?

    :w00t::hehe::w00t::hehe:


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (8/29/2012)


    Yeah, so now what I haven't tested it with is:

    Since you can use both OUTPUT and OUTPUT INTO within the same INSERT, UPDATE, DELETE or MERGE, can you also then use this OUTPUT as composable DML to INSERT into another table.

    So, one statement gives you:

    INSERT into 3 tables

    DELETE from 1 table, INSERT into 2

    UPDATE into 1, INSERT into 2

    MERGE into 1, INSERT into 2

    ?

    MERGE can also delete at the same time too can't it?

    :w00t::hehe::w00t::hehe:

    OUTPUT INTO doesn't appear to be nestable:

    USE tempdb

    GO

    DROP TABLE ABC

    CREATE TABLE ABC (Id INT, Name VARCHAR(10), [Level] VARCHAR(20))

    INSERT INTO ABC VALUES (1, 'PK','Already in table'), (2, 'SK','Already in table')

    DROP TABLE ABC1

    CREATE TABLE ABC1 (Id INT, Name VARCHAR(10), [Level] VARCHAR(20))

    INSERT INTO ABC1 VALUES (1, 'KKKKK','Already in table'), (2, 'MMMMM','Already in table')

    SELECT * FROM ABC

    SELECT * FROM ABC1

    -----------------------------------------------

    INSERT ABC1 (Id, Name, [Level])

    SELECT ID, Name, 'First insert'

    FROM (

    UPDATE A SET

    A.Name = 'PPPPP', a.[Level] = 'Innermost update'

    OUTPUT deleted.Id, inserted.Name, 'Output Insert' INTO ABC1

    OUTPUT deleted.Id, inserted.Name

    FROM ABC A

    JOIN ABC1 B ON A.Id = B.Id

    WHERE A.Id = 1

    ) d1

    SELECT * FROM ABC

    SELECT * FROM ABC1

    Msg 10720, Level 15, State 1, Line 17

    An OUTPUT INTO clause is not allowed in a nested INSERT, UPDATE, DELETE, or MERGE statement.

    Careful with this stuff, you could disappear...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • If you're willing to jump through a load of hoops, you can update both tables with a view

    CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('A') CHECK (Tab='A'), PRIMARY KEY(Id,Tab))

    INSERT INTO ABC(Id,Name) VALUES (1, 'PK'), (2, 'SK')

    CREATE TABLE ABC1 (Id INT NOT NULL, Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('B') CHECK (Tab='B'), PRIMARY KEY(Id,Tab) )

    INSERT INTO ABC1(Id,Name) VALUES (1, 'KKKKK'), (2, 'MMMMM')

    GO

    CREATE VIEW myView AS

    SELECT Tab,Id,Name

    FROM ABC

    UNION ALL

    SELECT Tab,Id,Name

    FROM ABC1

    GO

    SELECT Id,Name FROM ABC

    SELECT Id,Name FROM ABC1

    UPDATE myView

    SET Name = 'PPPPP'

    WHERE Id=1

    SELECT Id,Name FROM ABC

    SELECT Id,Name FROM ABC1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • purushottam2 (8/29/2012)


    I want to update in single query, other wise i have to use transaction and i can not use transaction.....

    Why can't you use a transaction?

  • Mark-101232 (8/29/2012)


    If you're willing to jump through a load of hoops, you can update both tables with a view

    CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('A') CHECK (Tab='A'), PRIMARY KEY(Id,Tab))

    INSERT INTO ABC(Id,Name) VALUES (1, 'PK'), (2, 'SK')

    CREATE TABLE ABC1 (Id INT NOT NULL, Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('B') CHECK (Tab='B'), PRIMARY KEY(Id,Tab) )

    INSERT INTO ABC1(Id,Name) VALUES (1, 'KKKKK'), (2, 'MMMMM')

    GO

    CREATE VIEW myView AS

    SELECT Tab,Id,Name

    FROM ABC

    UNION ALL

    SELECT Tab,Id,Name

    FROM ABC1

    GO

    SELECT Id,Name FROM ABC

    SELECT Id,Name FROM ABC1

    UPDATE myView

    SET Name = 'PPPPP'

    WHERE Id=1

    SELECT Id,Name FROM ABC

    SELECT Id,Name FROM ABC1

    Mark - Very interesting that you can do this with a VIEW. You can't apparently with a CTE.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (8/29/2012)


    Mark-101232 (8/29/2012)


    If you're willing to jump through a load of hoops, you can update both tables with a view

    CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('A') CHECK (Tab='A'), PRIMARY KEY(Id,Tab))

    INSERT INTO ABC(Id,Name) VALUES (1, 'PK'), (2, 'SK')

    CREATE TABLE ABC1 (Id INT NOT NULL, Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('B') CHECK (Tab='B'), PRIMARY KEY(Id,Tab) )

    INSERT INTO ABC1(Id,Name) VALUES (1, 'KKKKK'), (2, 'MMMMM')

    GO

    CREATE VIEW myView AS

    SELECT Tab,Id,Name

    FROM ABC

    UNION ALL

    SELECT Tab,Id,Name

    FROM ABC1

    GO

    SELECT Id,Name FROM ABC

    SELECT Id,Name FROM ABC1

    UPDATE myView

    SET Name = 'PPPPP'

    WHERE Id=1

    SELECT Id,Name FROM ABC

    SELECT Id,Name FROM ABC1

    Mark - Very interesting that you can do this with a VIEW. You can't apparently with a CTE.

    Have you noticed how ambiguous all the rules look in BOL, regarding updating views?

    Dwain - the error message you get with a CTE doesn't make much sense:

    Msg 4406, Level 16, State 1, Line 2

    Update or insert of view or function 'myView' failed because it contains a derived or constant field.

    - even if you change the table structure:

    DROP TABLE ABC

    CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab CHAR(1), PRIMARY KEY(Id,Tab))

    INSERT INTO ABC(Id,Name,Tab) VALUES (1, 'PK', 'A'), (2, 'SK', 'A')

    DROP TABLE ABC1

    CREATE TABLE ABC1 (Id INT NOT NULL, Name VARCHAR(10), Tab CHAR(1), PRIMARY KEY(Id,Tab) )

    INSERT INTO ABC1(Id,Name,Tab) VALUES (1, 'KKKKK','B'), (2, 'MMMMM','B')

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Yep. That's the same error I got.

    Whatever it is saying, it's odd.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (8/29/2012)


    Yep. That's the same error I got.

    Whatever it is saying, it's odd.

    It refers to "fields" too - and took me ages to call them "columns" when I moved from Visual Foxpro to SQL Server.

    Next time someone barks "They're not 'fields', they're 'columns'", you know what to do 😀

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply