Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with SQL to change data in multiple tables

Posted on 2014-04-02
3
Medium Priority
?
437 Views
Last Modified: 2014-04-02
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
Comment
Question by:HLRosenberger
  • 2
3 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 39972366
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
 
LVL 1

Author Comment

by:HLRosenberger
ID: 39972393
thanks.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 39972394
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question