How to Update a Two Table in Sql Server

Posted by Kirubakaranb.Tech under Sql Server on 12/11/2013 | Points: 10 | Views : 1805 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

how to update two table values in a single sql query

i have a two table as 'Tabl1' and 'Table2' and two table field are ID,Username is common for both table

Now i need a single update query to update both table

I try to write a code as two update query

EX:

update table1 set Username='abc' where Id=1
update table2 set Username='abc' where Id=1

but i need s single query to update a both table.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: vishalneeraj-24503 on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

0	Use UPDATE table1,table2 SET table1.col=a,table2.col2=b WHERE items.id=month.id; OR UPDATE table1 SET a=b WHERE c; UPDATE table2 SET a=b WHERE d; UPDATE table3 SET a=b WHERE e; semi-colon separated statement Kirubakaranb.Tech, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: vishalneeraj-24503 on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

0	Also refer:- UPDATE table1 INNER JOIN table2 USING (customer_id) SET table1.customer_id = 999, table2.customer_id = 999 WHERE table1.customer_id = 3 Kirubakaranb.Tech, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

0	we can't update more than one table data using single update statement..... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Kirubakaranb.Tech, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

0	alternately you can do with the help of views.... 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 * FROM myView SELECT Id,Name FROM ABC SELECT Id,Name FROM ABC1 -- to update both tables data using view UPDATE myView SET Name = 'PPPPP' WHERE Id=1 SELECT Id,Name FROM ABC SELECT Id,Name FROM ABC1 drop table ABC drop table ABC1 DROP VIEW myView Check http://www.sqlservercentral.com/Forums/Topic1351435-391-1.aspx Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Kirubakaranb.Tech, if this helps please login to Mark As Answer. \| Alert Moderator

alternately you can do with the help of views....


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 * FROM myView



SELECT Id,Name FROM ABC

SELECT Id,Name FROM ABC1



-- to update both tables data using view

UPDATE myView

SET Name = 'PPPPP'

WHERE Id=1

       

SELECT Id,Name FROM ABC

SELECT Id,Name FROM ABC1





drop table ABC

drop table ABC1

DROP VIEW myView

Check http://www.sqlservercentral.com/Forums/Topic1351435-391-1.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kirubakaranb.Tech, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

0	Hi,we can update more than one table as UPDATE table1 SET a=b WHERE condition; UPDATE table2 SET a=b WHERE condition; UPDATE table3 SET a=b WHERE condition; By Semi-Colon Separated statement,we can Update as well as delete records. Kirubakaranb.Tech, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

0	Yes vishal that is possible with more than one UPDATE statement; but not with SINGLE update statement Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Kirubakaranb.Tech, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts