Solved

Help with SQL to change data in multiple tables

Posted on 2014-04-02
3
432 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about Common Table Expressions 3 45
SQL query 45 41
SQL query joining 6 tables in asp.net 4 33
Query Task 8 23
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 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
Viewers will learn how the fundamental information of how to create a table.
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…

710 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