SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to update two tables data with JOIN in Single query


How to update two tables data with JOIN in Single query

Author
Message
purushottam2
purushottam2
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 101
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........
Suresh B.
Suresh B.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5114 Visits: 5330
You need to write two update statements.
purushottam2
purushottam2
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 101
I want to update in single query, other wise i have to use transaction and i can not use transaction.....
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18265 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42468 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18265 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42468 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18265 Visits: 6431
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?

w00tHehew00tHehe


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42468 Visits: 20012
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?

w00tHehew00tHehe


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
Exploring Recursive CTEs by Example Dwain Camps
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6603 Visits: 25602
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




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search