CODEWITHSUNDEEP
sql-server
user3335691
user3335691

Reputation: 31

Update multiple tables from query result

Is it possible to update multiple tables from a query result?

I've tried using a cursor. But it's still not working.

Here's the code :

DECLARE @TableName VARCHAR(MAX)

DECLARE db_cursor CURSOR FOR 
     SELECT TABLE_NAME  
     FROM information_schema.columns 
     WHERE column_name = 'Code1';

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN 
    UPDATE @TableName 
    SET Code1 = Code + '_' + Type

    FETCH NEXT FROM db_cursor INTO @TableName
END

CLOSE db_cursor
DEALLOCATE db_cursor

Upvotes: 1

Views: 131

Answers (1)

Alex Yu
Alex Yu

Reputation: 3537

Hypothesis

I suppose that OP is trying to dynamically build and execute SQL-code for all tables that have column Code1

Solution

Solution (one of many) could be:

  1. Build cursor of created SQL-expressions
  2. In cycle exec created expressions

Example code

DECLARE @sql_code varchar(max)

DECLARE code_cursor CURSOR FOR
SELECT DISTINCT 
       'UPDATE '+ TABLE_NAME + ' SET Code1= Code + ''_'' + Type;' AS SQL_CODE
     FROM 
         information_schema.columns -- WHERE column_name = 'Code1';

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @sql_code

WHILE @@FETCH_STATUS = 0
BEGIN 
    exec(@sql_code)

    FETCH NEXT FROM db_cursor INTO @TableName
END
CLOSE db_cursor
DEALLOCATE db_cursor

Caution

I did not tested it (of cause - I don't have similar DB) - so be careful.

Update

It's even simpler would be to modify OP code like this:

DECLARE @TableName VARCHAR(MAX)

DECLARE db_cursor CURSOR 
FOR SELECT DISTINCT TABLE_NAME   -- note DISTINCT here
    FROM information_schema.columns WHERE column_name = 'Code1'; 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN 
EXEC('UPDATE '+ @TableName + ' SET Code1 = Code + ''_'' + Type') -- note EXEC here

FETCH NEXT FROM db_cursor INTO @TableName
END
CLOSE db_cursor
DEALLOCATE db_cursor

Upvotes: 2

Related Questions