How to Update a Two Table in Sql Server

Posted by Kirubakaranb.Tech under Sql Server on 12/11/2013 | Points: 10 | Views : 921 | Status : [Member] | Replies : 6
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.




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response