0

I have around 50 tables in my database. In all tables where there is userid column (Not all the tables contain this column), I need to change the value of it from "User1" to "User2". This query would be re-used many times with changing values of "User1" and "User2"

Ram Grandhi
  • 397
  • 10
  • 27
  • Would `ON UPDATE CASCADE` be applicable in your situation? [This question](http://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade "When to use “ON UPDATE CASCADE”") is about when it can or cannot be used (in case you need more info). – Andriy M Jun 02 '14 at 08:49
  • ON UPDATE CASCADE is not applicable in this situation. And thanks for the link. – Ram Grandhi Jun 02 '14 at 13:34

2 Answers2

2

Probably create a stored procedure to do the same like

create procedure sp_update_table(@tbl_name varchar(30))
as
begin
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = N'UPDATE ' + QUOTENAME(@tbl_name ) + 
'SET userid='User2' WHERE userid='User1''
EXEC sp_executesql @sql
end

then just call your procedure as many times you want passing the table name like

exec sp_update_table('mytable')

EDIT:

You can easily find all tables which contains userid column from INFORMATION_SCHEMA.COLUMNS as below

Use [DatabaseName]
Select table_name From INFORMATION_SCHEMA.COLUMNS Where column_name = 'userid'
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Thanks for the procudere Rahul, But the query first needs to find the table which contains the tables which contains the userid column – Ram Grandhi May 30 '14 at 16:56
0

Write 50 update statements:

UPDATE <TABLE NAME> 
SET userid='User2' 
WHERE userid='User1'

It should be easy enough to generate these in a simple text editor and then paste into SQL Server Management Studio.

rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • It is not like that, I gave user1 and user2 only as an example. This statement would be used multiple times and values of user1 and user2 will change. Moreover all the tables doesn't contain that column. The query needs to check which columns has that field and then update accordingly – Ram Grandhi May 30 '14 at 16:46
  • @Ram -- then why didn't you *say all that* in your question? I just answered the question as it was written; we can't read minds. – rory.ap May 30 '14 at 16:47
  • I already mentioned in the question saying that "In all tables where there is userid column" – Ram Grandhi May 30 '14 at 16:53