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 ShawFor 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 tempdbGO
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 ShawFor 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 17An 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 ShawFor 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 viewCREATE 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 viewCREATE 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 2Update 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 ABCCREATE 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 ShawFor 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 ShawFor 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