How to Update this Situation for SQL SERVER

Posted by Jayakumars under Sql Server on 8/6/2019 | Points: 10 | Views : 1112 | Status : [Member] [MVP] | Replies : 3
create table table1
(
ID int,
aname nvarchar(100),
adesc nvarchar(1000
)
GO
create table table2
(
ID int,
bname nvarchar(100),
bdesc nvarchar(1000
)
go

/*
HERE HOW TO UPDATE MULTIPLE COLUMN AND MULTIPLE TABLE LIKE THIS

UPDATE ANAME='AA',BNAME='BB' WHERE ID=ID
*/

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

Posted by: Jayakumars on: 8/28/2019 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Poojasri

did you seen my question carefully

it is not update single table

can we multiple table update in single query ?

if yes means share the sample code
or can you share with me my mail id : kumaraspcode2009@gmail.com

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Posted by: Anand9796 on: 9/24/2019 [Member] Starter | Points: 25

Up
0
Down
I think SQL Server does not allow to update more than 1 table at a time with a single UPDATE statement. The alternatives may be to use trigger AFTER UPDATE to update the second table or a view and INSTEAD OF trigger to update both tables even you can use TRANSACTION also

Software Developer at iFour Technolab Pvt Ltd
http://ifourtechnolab.com

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

Posted by: Douglasphillips24 on: 10/1/2019 [Member] Starter | Points: 25

Up
0
Down


You can't update two tables at once, but you can link an update into an insert using OUTPUT INTO, and you can use this output as a join for the second update:

DECLARE @ids TABLE (id int);
BEGIN TRANSACTION

UPDATE Table1
SET Table1.LastName = 'DR. XXXXXX'
OUTPUT INSERTED.id INTO @ids
WHERE T1.field = '010008';

UPDATE Table2
SET Table2.WAprrs = 'start,stop'
FROM Table2
JOIN @ids i on i.id = Table2.id;

COMMIT;


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

Login to post response