Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to update two tables data with JOIN in Single query Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 1:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, 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........
Post #1351435
Posted Wednesday, August 29, 2012 1:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
You need to write two update statements.
Post #1351436
Posted Wednesday, August 29, 2012 1:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
I want to update in single query, other wise i have to use transaction and i can not use transaction.....
Post #1351439
Posted Wednesday, August 29, 2012 3:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1351485
Posted Wednesday, August 29, 2012 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1351495
Posted Wednesday, August 29, 2012 4:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1351499
Posted Wednesday, August 29, 2012 4:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1351500
Posted Wednesday, August 29, 2012 4:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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?




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!
Post #1351506
Posted Wednesday, August 29, 2012 5:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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?



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
Post #1351518
Posted Wednesday, August 29, 2012 5:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 1,682, Visits: 19,602
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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1351520
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse