How to Update information in multiple tables in the same database

Question

How to Update information in multiple tables in the same database

Rthomas

Grasshopper

Points: 19
More actions
April 5, 2005 at 1:10 pm

#67380

I am currently trying to find the best way to update information in multiple tables of the same database. The column name it timekeeper (Varchar (15) null). This field is currently in 12 tables. What would be the best way to change the timekeeper ID from AKB to 00023 in all 12 tables?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Run this in query analyser, then paste the results on this query in QA and run the statements.

Select 'UPDATE [' + user_name(O.uid) + '].[' + O.Name + '] SET timekeeper = ''00023'' WHERE TimerKeeper = ''AKB''' as ExecQueries from dbo.SysObjects O inner join SysColumns C ON O.id = C.id and C.Name = 'TimerKeeper' and O.XType = 'U'

Rthomas Grasshopper Points: 19 More actions · Answer 2

PERFECT

Thank you!!

I had to make a minor change to the query in order for it to run properly. Your query had C.Name = 'TimerKeeper' instead of Timekeeper. No biggie and thank you for the help. Below is the Query as it should be

Select 'UPDATE [' + user_name(O.uid) + '].[' + O.Name + '] SET timekeeper = ''00023'' WHERE TimerKeeper = ''AKB''' as ExecQueries from dbo.SysObjects O inner join SysColumns C ON O.id = C.id and C.Name = 'TimeKeeper' and O.XType = 'U'

Here are the results of the query above

UPDATE [dbo].[time] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[groupsecure] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[synched] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[persclients] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[persmatters] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[persnarrative] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[recentcm] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[default] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[timekeepers] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[timesaver] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[usersecure] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[dterestrict] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[SORT] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[looklog] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[activetimers] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[startstoptimes] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[offlinelog] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[goaldata] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

UPDATE [dbo].[goalgroupdata] SET timekeeper = '00023' WHERE TimerKeeper = 'AKB'

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

April 5, 2005 at 2:09 pm

#550642

Glad you could work it out.

Rthomas Grasshopper Points: 19 More actions · Answer 4

Remi,

The query looked like it was working but in fact it didn't update any of the information. This may be due in small part to the fact the the table name is TIMEKEEPERS and the Column name is TIMEKEEPER. Please advise.

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 5

In the where clause the columnname is currently timeRkeeper. Remove the extra R?

G'day

Wayne

Rthomas Grasshopper Points: 19 More actions · Answer 6

Wayne,

Found that and fixed that. Thanks for pointing that out.