Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Help with SQL to change data in multiple tables

I have a job table with a "jobID" as primary key.   I have multiple related tables that have a "jobID" foreign key column.  I need to correct bad "jobID" data across all the tables.   The name "jobID" is used in all the tables.  

This query (found online) gives me all the tables that contain the "jobID" column.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%jobID%'
ORDER BY schema_name, table_name;
0
HLRosenberger
Asked:
HLRosenberger
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
E.g.

WITH    AffectedTables
          AS ( SELECT   QUOTENAME(S.name) + '.' + QUOTENAME(T.name) AS table_name ,
                        QUOTENAME(c.name) AS column_name
               FROM     sys.columns C
                        INNER JOIN sys.tables T ON C.object_id = T.object_id
                        INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
               WHERE    C.name LIKE '%ID' -- %jobID%'
             )
    SELECT  'UPDATE ' + table_name + ' SET ' + column_name + ' = 123 WHERE ' + column_name + ' = 666;' AS update_statement
    FROM    AffectedTables
    ORDER BY table_name;

Open in new window


Caveat: Make a backup first.

As foreign key constraints and triggers may be a problem you should test your entire update script by enclosing it into explicit transaction control with TRY-CATCH.
0
 
HLRosenbergerAuthor Commented:
thanks.
0
 
HLRosenbergerAuthor Commented:
thanks
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now