6 Replies - 1816 Views - Last Post: 22 March 2012 - 03:56 PM

#1 hoffmn   User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 46
  • Joined: 22-October 09

Question about updating tables

Posted 21 March 2012 - 10:42 AM

I have a table that has a lot of high schools in it. A lot of the names have been misspelled. We are going through and finding the correct high schools according to the DoE and my job will be to do an update on this table to fix it. Each HS has an internal UID associated to it. What I will need to do is come up with a script that will essentially join a table showing the HS names and UID and show the incorrect misspellings and UIDs along side it. Then I want to update the incorrect UID with the correct one. Main issue here is that there are a ton of dependencies attached to this table. Will I need to incorporate all of these dependencies in the update as well or is there an easier way to search for the dependencies in the update query I will be writing?

This post has been edited by hoffmn: 21 March 2012 - 10:43 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Question about updating tables

#2 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Question about updating tables

Posted 21 March 2012 - 03:41 PM

If the database is designed properly, then any FK constraints against it will be against the UID rather than the name. The name should just be a value attribute, rather than a relational attribute, so changing the name shouldn't present a problem.

What database are you using, MySQL, MSSQL, Oracle, etc? It is possible to run checks to see what references a column, to assess the impact changes will make to your schema.
Was This Post Helpful? 0
  • +
  • -

#3 hoffmn   User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 46
  • Joined: 22-October 09

Re: Question about updating tables

Posted 21 March 2012 - 04:16 PM

We need to change the UIDs, not the names. We use MSSQL. What is the check I can use to check the column? I know the UIDs for the high schools are referenced in at least one other table. I need to find out just how many though. I appreciate any help I can get.
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Question about updating tables

Posted 21 March 2012 - 06:53 PM

This query will get you all the references to the UID column on the schools table. You will have to substitute in the name of the schools table as I don't know what it is:
SELECT
	OBJECT_NAME(keycols.parent_object_id) AS ParentTable,
	parent.name AS ParentColumn,
	OBJECT_NAME(keycols.referenced_object_id) AS ReferencedTable,
	reference.name AS ReferencedColumn,
	keys.update_referential_action_desc AS UpdateAction
FROM sys.foreign_key_columns AS keycols
INNER JOIN sys.columns AS parent
	ON parent.column_id = keycols.parent_column_id
	AND parent.object_id = keycols.parent_object_id
INNER JOIN sys.columns AS reference
	ON reference.column_id = keycols.referenced_column_id
	AND reference.object_id = keycols.referenced_object_id
INNER JOIN sys.foreign_keys keys
	ON keys.object_id = keycols.constraint_object_id
	AND keys.parent_object_id = keycols.parent_object_id
	AND keys.referenced_object_id = keycols.referenced_object_id
WHERE OBJECT_NAME(keycols.referenced_object_id) = '??' -- Insert the table name of the schools
AND reference.name = 'UID'


Any rows that have UpdateAction of 'CASCADE', you don't need to worry about, they will update themselves.

Keep in mind that this will only check foreign key constraints. If you have columns that are meant to JOIN on UID, and don't have a FK constraint in place, will not be in that list.

I would suggest building a mapping table that you can UPDATE from using a SELECT, just in case your application breaks due to non-constrained referential data.
Was This Post Helpful? 1
  • +
  • -

#5 hoffmn   User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 46
  • Joined: 22-October 09

Re: Question about updating tables

Posted 22 March 2012 - 07:23 AM

So these are my results. Thanks a lot, there is a lot less work ahead for me with this project. Viewing dependencies gives a lot of results, but this is fine tuned to give me exactly what I need. Kudos e_i_pi. I am creating a stored procedure out of this now and making it work with variables so we can run it for anything we want in the future.

ParentTable	ParentColumn	ReferencedTable	ReferencedColumn	UpdateAction
SpeedeImportPerson HighSchoolID	HighSchools	HighSchoolID	NO_ACTION
PreAdmission	HighSchoolID	HighSchools	HighSchoolID	NO_ACTION


What does the NO_ACTION update action mean though?

This post has been edited by hoffmn: 22 March 2012 - 07:25 AM

Was This Post Helpful? 0
  • +
  • -

#6 hoffmn   User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 46
  • Joined: 22-October 09

Re: Question about updating tables

Posted 22 March 2012 - 07:47 AM

So, with e_i_pi's permission, I am posting the stored procedure I compiled using the query he gave me. You will need an instance of it in each database you have, in my case I created one for both of the databases I use mostly.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<NAME>
-- Create date: <DATE>
-- Description:	This stored procedue executes a 
--				query that will find all tables
--				referencing the specified column
--				from the specified table.
-- =============================================
CREATE PROCEDURE dbo.pr_ColumnLookup
	@Table AS VARCHAR(50) = ''
	, @Column AS VARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;

IF @Table = ''
BEGIN
	PRINT ''
	PRINT 'pr_ColumnLookup is a stored procedure to find all the references to the column'
	PRINT 'specified from the table you enter. Please enter a table name.'
	PRINT ''
	PRINT 'example: @Table = ''HighSchools'''
	PRINT ''
	RETURN --abort procedure
END
	
	IF @Column = ''
BEGIN
	PRINT ''
	PRINT 'pr_ColumnLookup is a stored procedure to find all the references to the column'
	PRINT 'specified from the table you enter. Please enter a column name.'
	PRINT ''
	PRINT 'example: @Column = ''HighSchoolID'''
	PRINT ''
	RETURN --abort procedure
END

SELECT
	OBJECT_NAME(keycols.parent_object_id) AS ParentTable,
	parent.name AS ParentColumn,
	OBJECT_NAME(keycols.referenced_object_id) AS ReferencedTable,
	reference.name AS ReferencedColumn,
	keys.update_referential_action_desc AS UpdateAction
FROM sys.foreign_key_columns AS keycols
INNER JOIN sys.columns AS parent
	ON parent.column_id = keycols.parent_column_id
	AND parent.object_id = keycols.parent_object_id
INNER JOIN sys.columns AS reference
	ON reference.column_id = keycols.referenced_column_id
	AND reference.object_id = keycols.referenced_object_id
INNER JOIN sys.foreign_keys keys
	ON keys.object_id = keycols.constraint_object_id
	AND keys.parent_object_id = keycols.parent_object_id
	AND keys.referenced_object_id = keycols.referenced_object_id
WHERE OBJECT_NAME(keycols.referenced_object_id) = @Table -- Variable for the Table to key off of
AND reference.name = @Column -- Variable for the columnn to key off of

END


Was This Post Helpful? 0
  • +
  • -

#7 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Question about updating tables

Posted 22 March 2012 - 03:56 PM

View Posthoffmn, on 23 March 2012 - 12:23 AM, said:

What does the NO_ACTION update action mean though?

NO_ACTION means that if you change the UID, it won't change in the FK related table. For instance, if you have a PK called ID in table Parent, and then you have a FK ParentID in a table Child, then updating a value of ID in Parent won't cascade down to updating the ParentID to the same value in child. I hope I explained that clearly, I feel like I didn't :/

View Posthoffmn, on 23 March 2012 - 12:47 AM, said:

So, with e_i_pi's permission, I am posting the stored procedure I compiled using the query he gave me. You will need an instance of it in each database you have, in my case I created one for both of the databases I use mostly.

Uh, you don't need my permission hehe, but I'll have a look.

Quote

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<NAME>
-- Create date: <DATE>
-- Description:	This stored procedue executes a 
--				query that will find all tables
--				referencing the specified column
--				from the specified table.
-- =============================================
CREATE PROCEDURE dbo.pr_ColumnLookup
	@Table AS VARCHAR(50) = ''
	, @Column AS VARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;

IF @Table = ''
BEGIN
	PRINT ''
	PRINT 'pr_ColumnLookup is a stored procedure to find all the references to the column'
	PRINT 'specified from the table you enter. Please enter a table name.'
	PRINT ''
	PRINT 'example: @Table = ''HighSchools'''
	PRINT ''
	RETURN --abort procedure
END
	
	IF @Column = ''
BEGIN
	PRINT ''
	PRINT 'pr_ColumnLookup is a stored procedure to find all the references to the column'
	PRINT 'specified from the table you enter. Please enter a column name.'
	PRINT ''
	PRINT 'example: @Column = ''HighSchoolID'''
	PRINT ''
	RETURN --abort procedure
END

SELECT
	OBJECT_NAME(keycols.parent_object_id) AS ParentTable,
	parent.name AS ParentColumn,
	OBJECT_NAME(keycols.referenced_object_id) AS ReferencedTable,
	reference.name AS ReferencedColumn,
	keys.update_referential_action_desc AS UpdateAction
FROM sys.foreign_key_columns AS keycols
INNER JOIN sys.columns AS parent
	ON parent.column_id = keycols.parent_column_id
	AND parent.object_id = keycols.parent_object_id
INNER JOIN sys.columns AS reference
	ON reference.column_id = keycols.referenced_column_id
	AND reference.object_id = keycols.referenced_object_id
INNER JOIN sys.foreign_keys keys
	ON keys.object_id = keycols.constraint_object_id
	AND keys.parent_object_id = keycols.parent_object_id
	AND keys.referenced_object_id = keycols.referenced_object_id
WHERE OBJECT_NAME(keycols.referenced_object_id) = @Table -- Variable for the Table to key off of
AND reference.name = @Column -- Variable for the columnn to key off of

END


That should be fine. Generally you use stored procedures to perform a function on a database, but parameterised datasets is also a valid use.

I think I mentioned this before, but make sure you have a test environment to test this on first. This means a copy of the DB as well as a copy of the application. If something goes awry, then you haven't lost your data.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1