Solved

Help with SQL to change data in multiple tables

Posted on 2014-04-02
3
434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
ste5an earned 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

623 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