March 4, 2011 at 6:20 am
OK, this is ugly and as a newbie (since Last April) in SQL and this old RBAR programmer is having trouble figuring out how to:
I have a column in 80+ tables called trm_cde. I also have a table called TablesWithTrmCde that has 1 column in it named TableToUse with the above mentioned 80+ tables.
The requirement is to change the trm_cde in any table that is currently 'S1' or 'S2' to 'S'
I am having trouble figuring out how to generate the SQL commands to update each table and do them in the correct order because of Foreign Keys.
Ideally I'd like to generate Select and Update statements for each table.
March 4, 2011 at 7:11 am
well it sounds like th trm_cde is a lookup value, correct?
so that table which contains all the possible trm_cde would have foreign keys from those 80 other tables, right?
one of the system views sysforeignkeys has the information you can use to generate your update scripts.
SELECT
'UPDATE '
+ OBJECT_NAME(fkeyid)
+ ' SET '
+ COL_NAME(fkeyid,fkey)
+ ' = ''S'' WHERE '
+ COL_NAME(fkeyid,fkey)
+ ' IN (''S1'',''S2'')' AS cmd
from sysforeignkeys
--WHERE OBJECT_NAME(rkeyid)= 'TBSTATE'
-- AND COL_NAME(rkeyid,rkey) = 'STATETBLKEY'
WHERE OBJECT_NAME(rkeyid)= 'yourLookupTable'
AND COL_NAME(rkeyid,rkey) = 'trm_cde'
Lowell
March 4, 2011 at 8:00 am
That worked for the most part but some are missing
Example:
Table CourseHistory has trm_cde in it but it has a 3 column FK, with trm_cde being one of them.
That 3 column key is the PK to table TermSummary which has a FK of just trm_cde that maps back to the trm_def table
March 4, 2011 at 8:05 am
what you would need is just a variation of the above example, but with the three foreign key columns involved; can you paste the definition of the primary key/unique constraint that is being used for the foreign key's definition on CourseHistory and TermSummary , so that the three columns can be selected in sysforeignkeys?
Lowell
March 4, 2011 at 9:46 am
Given that you have a table that lists all the tables that have this column (assuming the column trm_cde is identically named in all of them). A slight modification to Lowell's idea should do the trick.
select 'Update ' + TableToUse + ' set trm_cde = ''S'' where trm_cde in (''S1'', ''S2'')'
from TablesWithTrmCde
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2011 at 9:53 am
Sadly, I've got another 20+ tables that have the trm_cde as 1 of the key columns. The other keys could be any combination of columns with trm_cde being one of them. I am running a script now that another developer helped me with so we'll see how it goes
March 4, 2011 at 10:03 am
You could change the from in my previous post to
from sysobjects so
join syscolumns sc on so.id = sc.id
where sc.name = 'trm_cde'
and so.xtype = 'U'
That would get you a list of all table names with a column named trm_cde
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2011 at 10:05 am
To be more precise and maybe add a little clarity to what i was trying to convey...
select 'Update ' + so.name + ' set trm_cde = ''S'' where trm_cde in (''S1'', ''S2'')'
from sysobjects so
join syscolumns sc on so.id = sc.id
where sc.name = 'trm_cde' and so.xtype = 'U'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2011 at 10:17 am
The problem is more complicated if you have multiple levels of foreign keys.
Also the foreign keys may not have the same column names as the referenced column.
The following uses recursion to show the dependancies.
(I have used INFORMATION_SCHEMA but sysforeignkeys will have the same effect.)
You may want to create a test database for the test data.
-- *** Test Data ***
CREATE TABLE trm_def
(
trm_cde varchar(10) NOT NULL
PRIMARY KEY
)
INSERT INTO trm_def
VALUES ('S1'), ('S2')
CREATE TABLE TermSummary
(
Col1 int NOT NULL
,Col2 int NOT NULL
,trm_cde varchar(10) NOT NULL
REFERENCES trm_def(trm_cde)
,trm_cdeX varchar(10) NOT NULL
REFERENCES trm_def(trm_cde)
PRIMARY KEY (Col1, Col2, trm_cde)
,UNIQUE (Col1, Col2, trm_cdeX)
)
INSERT INTO TermSummary
VALUES (1, 1, 'S1', 'S2')
,(1, 1, 'S2', 'S1')
,(2, 1, 'S1', 'S2')
,(2, 1, 'S2', 'S1')
CREATE TABLE CourseHistory
(
CourseHistory_Id int NOT NULL
PRIMARY KEY
,Col1 int NOT NULL
,Col2 int NOT NULL
,trm_cde varchar(10) NOT NULL
,trm_cde2 varchar(10) NOT NULL
,FOREIGN KEY (Col1, Col2, trm_cde) REFERENCES TermSummary (Col1, Col2, trm_cde)
,FOREIGN KEY (Col1, Col2, trm_cde2) REFERENCES TermSummary (Col1, Col2, trm_cdeX)
)
INSERT INTO CourseHistory
VALUES (1, 1, 1, 'S1', 'S2')
,(2, 1, 1, 'S2', 'S1')
,(3, 2, 1, 'S1', 'S2')
,(4, 2, 1, 'S2', 'S1')
-- *** End Test Data ***
;WITH FKeys
AS
(
SELECT FK.TABLE_SCHEMA AS fk_schema
,FK.TABLE_NAME AS fk_table
,FK.COLUMN_NAME AS fk_column
,REF.TABLE_SCHEMA AS ref_schema
,REF.TABLE_NAME AS ref_table
,REF.COLUMN_NAME AS ref_column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK
ON C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE REF
ON C.UNIQUE_CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA
AND C.UNIQUE_CONSTRAINT_NAME = REF.CONSTRAINT_NAME
AND FK.ORDINAL_POSITION = REF.ORDINAL_POSITION
)
, FKOrder
AS
(
SELECT DISTINCT
ref_schema AS rschema
,ref_table AS rtable
,ref_column AS rcolumn
,1 AS rLevel
FROM FKeys F1
WHERE ref_column = 'trm_cde'
AND NOT EXISTS
(
SELECT *
FROM FKeys F2
WHERE F2.fk_schema = F1.ref_schema
AND F2.fk_table = F1.ref_table
AND F2.ref_column = 'trm_cde'
)
UNION ALL
SELECT
F1.fk_schema AS rschema
,F1.fk_table AS rtable
,F1.fk_column AS rcolumn
,F2.rLevel + 1
FROM FKeys F1
JOIN FKOrder F2
ON F2.rschema = F1.ref_schema
AND F2.rtable = F1.ref_table
AND F2.rcolumn = F1.ref_column
)
SELECT *
FROM FKOrder
Once you have the effected columns, and their position in the hierachy, you can then:
1. start inserting the new rows working down the hierachy. (At some levels you may need to combine the rows with the old values.)
INSERT INTO trm_def
VALUES ('S')
INSERT INTO TermSummary
VALUES (1, 1, 'S', 'S')
,(2, 1, 'S', 'S')
2. at the bottom of the hierachy you can then either update the rows or insert/delete the rows depending on what is appropriate.
UPDATE CourseHistory
SET trm_cde = CASE WHEN trm_cde IN ('S1', 'S2') THEN 'S' ELSE trm_cde END
,trm_cde2 = CASE WHEN trm_cde2 IN ('S1', 'S2') THEN 'S' ELSE trm_cde2 END
WHERE trm_cde IN ('S1', 'S2') OR trm_cde2 IN ('S1', 'S2')
3. start deleting the old rows working up the hierachy.
DELETE TermSummary
WHERE trm_cde IN ('S1', 'S2') OR trm_cdeX IN ('S1', 'S2')
DELETE trm_def
WHERE trm_cde IN ('S1', 'S2')
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply