How to perform multiple updates in stored procedure?

Question

I will update some columns and some rows from table1 to table2 togetherly based of model column.

ex. table1 in DATA1DB database

| id | name | address | color | model |
| 1    avi     aadd     blue     mod1
| 2    bref    ddff     red      mod2
| 3    cind    ffdd     red      mod1
| 4    davi    ffgg     green    mod1

table2 in DATA2DB database

| id | name | address | color | model |
| 1    avi     aadd     red      mod1
| 2    bref    dddd     red      mod2
| 3    cind    ffff     red      mod1
| 4    davi    gggg     red      mod1

when execute update based id=(1,3,4), i want table2 to be the same as table1. in my store procedure i have this code

ALTER PROCEDURE [dbo].[updatemultiple]
@id varchar (5)
AS BEGIN SET NOCOUNT ON
begin 
UPDATE  DATA2DB.table2
SET [DATA2DB].table2.address= [DATA1DB].table1.address,
    [DATA2DB].table2.color  = [DATA1DB].table1.color,
FROM [DATA2DB].table2
INNER JOIN [DATA1DB].table1
ON [DATA2DB].table2.id = [DATA1DB].table2.id
where LTRIM(RTRIM([DATA1DB].table1.id)) = LTRIM(RTRIM(@id))

I want result table2 in DATA2DB database

| id | name | address | color | model | 
  1     avi    aadd      blue    mod1 
  3    cind    ffdd      red     mod1 
  4     davi   ffgg     green    mod1

Not really clear what it is you're asking here. Desired results would probably help clarify that. Also, why do you have two different tables for the same entity? This is a bad design that can only lead to problems.... It would be much better to have a single table for each entity. — Zohar Peled, Nov 06 '18 at 07:01
table2 in different database. result | id | name | address | color | model | 1 avi aadd blue mod1 3 cind ffdd red mod1 4 davi ffgg green mod1 — San Dhopi, Nov 06 '18 at 07:15
Please [edit] your question to include the expected results, it's hard to read it in the comments. — Zohar Peled, Nov 06 '18 at 07:17
@SanDhopi: If an answer is correct, please mark it as accepted. — Dan Stef, Nov 07 '18 at 06:38

score 1 · Answer 1 · answered Nov 06 '18 at 08:40

I assume you want to update the DB2 whenever the DB1 gets updated.

If so, you can add an update trigger to your table in DB1.

ALTER TRIGGER [dbo].[tr_UPDATE_DB2TABLE]
  ON [DATA1DB].table1
AFTER UPDATE
AS BEGIN
 SET NOCOUNT ON;

 UPDATE  DATA2DB.table2
 SET [DATA2DB].table2.address= [DATA1DB].table1.address,
    [DATA2DB].table2.color  = [DATA1DB].table1.color,
 FROM [DATA2DB].table2
 INNER JOIN Inserted I ON I.Id = [DATA1DB].table1.Id
 INNER JOIN [DATA1DB].table1 ON [DATA2DB].table2.id = [DATA1DB].table2.id
 where LTRIM(RTRIM([DATA1DB].table1.model)) = LTRIM(RTRIM(I.model))
END

Each time the table in DB1 gets updates it will update the table in DB2.

Dan Stef · Answer 2 · 2018-11-06T13:44:11.327

Took me a while to understand what your problem is... If I understand you right, you want to execute your procedure with @id=('1,3,4')

Then you want to take each single id (1 and 3 and 4) and run the update for each id with the value from table1.. If this is all correct your sp could look like this:

ALTER PROCEDURE [dbo].[updateMultiple] @id VARCHAR(5)
AS
    BEGIN
        DECLARE @single_id INT;

        DECLARE my_cursor CURSOR
        FOR SELECT value
           FROM   STRING_SPLIT(@id, ',')
           WHERE  RTRIM(value) <> '';
        OPEN my_cursor;
        FETCH NEXT FROM my_cursor INTO @single_id;

        WHILE @@FETCH_STATUS = 0
        BEGIN
           UPDATE [db2]
            SET  
                [db2].[address] = [db1].[address], 
                [db2].[color] = [db1].[color]
           --    declare @id varchar(5) = (1,3,4);Select *
           FROM   [data2db] [db2]
                JOIN [data1db] [db1] ON [db2].[id] = [db1].[id]
           WHERE  [db1].[id] = @single_id;
           FETCH NEXT FROM my_cursor INTO @single_id;
        END;

        CLOSE my_cursor;
        DEALLOCATE my_cursor;

    END;
GO

You need to split your input string into each single id and perform the update..

This is possible, but probably not the best way to do it... Just saying...

Hope it helps.

PS: In my example, I've used two tables in the same DB, but it should work over two different databases as well.

EDIT: Or it should even work without cursor:

ALTER PROCEDURE [dbo].[updateMultiple] @id VARCHAR(5)
AS
    BEGIN
        UPDATE [db2]
         SET  
            [db2].[address] = [db1].[address], 
            [db2].[color] = [db1].[color]    
        --declare @id varchar(5) = '1,3,4';Select *
        FROM   [data2db] [db2]
             JOIN [data1db] [db1] ON [db2].[id] = [db1].[id]
        WHERE  [db1].[id] IN
        (
           SELECT value
           FROM   STRING_SPLIT(@id, ',')
           WHERE  RTRIM(value) <> ''
        );

    END;
GO

How to perform multiple updates in stored procedure?

2 Answers2