Solved

Tricky SQL Query

Posted on 2008-10-02
3
237 Views
Last Modified: 2010-03-20
Experts,

I'm performing a small migration in SQL Server and am wondering about how to update a common field in a set of tables.

The field that exists in about 10 tables in this database is LOCATION_ID.  I want to do a global updatge that essentially says that for every LOCATION_ID field in all the tables with the word BUSINESS in the name to have the value changed from a 1 to a 2 (where the value is 1).

How would I do this?
0
Comment
Question by:NigelRocks
  • 2
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22631178
you will need to use a transaction if you want all tables updated together:

begin tran
begin try
update table1 set location_id=2 where location_id=1
update table2 set location_id=2 where location_id=1
update table3 set location_id=2 where location_id=1
update table4 set location_id=2 where location_id=1
update table5 set location_id=2 where location_id=1
...
commit
end try
begin catch
select error_number(), error_message()
rollback
end catch
0
 
LVL 4

Accepted Solution

by:
randy_knight earned 500 total points
ID: 22631188
One way to do it would be to query syscolumns to get a list of the tables with LOCATION_ID in them. Then cursor through the tables running an update statement for each table. Use dbo.sp_executesql to dynamically build your statement. Or you could just print the statement to the results then copy and paste.

Here's an example using a column named "body" in the msdb database.

------- code start -----------
declare  @TableName sysname, @sql nvarchar(4000)

declare curTable cursor for
     select o.name
     from msdb.dbo.syscolumns c
            inner join msdb.dbo.sysobjects o on o.id = c.id
      where c.name = 'body'
      order by 1
open curTable
while 1=1
begin
         fetch next from curTable into @TableName
         if @@fetch_status <> 0
            break
         print 'update msdb.dbo.' + @TableName + ' set body = 2'
end
close curTable
deallocate curTable
-----------end code ------------
if you wanted to  go ahead and execute it in the cursor just put the dynamic squl in the @sql variable and call dbo.sp_executesql instead of the print statement.




 
0
 
LVL 4

Assisted Solution

by:randy_knight
randy_knight earned 500 total points
ID: 22631198

One more thing.&nbsp; You said the table would have BUSINESS in the name.&nbsp; So your select statement for the cursor would look like this:

select o.name
from dbo.syscolumns c
inner join dbo.sysobjects o on o.id = c.id
where c.name = 'LOCATION_ID'
and o.name like '%BUSINESS%'
order by 1&nbsp;
&nbsp;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 33
SQL Help 27 57
T-SQL Default value in Select? 5 39
Check ALL SP in database make sure there are no errors 17 46
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

821 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