SQL Server – Update All Tables Containing Column Name with Specific Field

sql serversql-server-2012

I know a similar question has been asked before but i was wonder if there is any way that you could run an update across all tables in a database where a column name exists but only for fields with a specific entry.

So for example UPDATE <table> SET <column_name> = '123' where <column_name> = '124'

Thanks in advance for everyones help

Best Answer

This answer will require some dynamic SQL and potentially could make use of a while/do loop.
Basically the idea will be to figure our how to define your list of tables and column names. If you really are just looking for a list of tables where a column name exists, you will probably want to make use of the sys.syscolumns table to query the column names.

SELECT o.name
FROM sys.syscolumns c
    JOIN sys.sysobjects o ON c.id=o.id
WHERE UPPER(c.name) = 'Description'

From there you can build your dynamic sql statement.

DECLARE @sqltext VARCHAR(MAX)
DECLARE @UpdateValue VARCHAR(30) = 'SetThisValue'

SELECT @sqltext = ISNULL(@sqltext,'') + 'UPDATE '+o.name + CHAR(10) + ' SET     [YourColumnNameHere] = ' + @UpdateValue + CHAR(10)
    + ' WHERE [YourColumnNameHere] = [Somevalue]'
FROM sys.syscolumns c
    JOIN sys.sysobjects o ON c.id=o.id
WHERE UPPER(c.name) = 'Description'

PRINT @sqltext
EXEC sp_executesql @sqltext

I have the print statement in there to help you get the @sqltext exactly the way you want it. Once you have it as you want, use the EXEC sp_executesql line to actually run the output of the @sqltext.

Related Solutions

Sql-server – Should I use many single field indexes, instead of specific multi column indexes

What you need are covering indexes, ie. indexes that can satisfy a query on their own. But a 'covering' index has one problem: it is covering a specific query. So in order to develop a good indexing strategy, you need to understand your workload: what queries are hitting the database, which ones are critical and which ones are not, how often each type of query is run, etc etc etc. And then you balance this against the write and update cost of each index, and there you have your indexing strategy. If it sounds complicated that is because it is complicated.

However you can apply some rules of thumb. The MSDN covers the basics quite well:

There is also a myriad of articles contributed by the community, eg. Webcast Recording – DBA Darwin Awards: Index Edition.

And to answer your question specifically: separate indexes on each column can work, provided that each column has a high selectivity (many distinct values, each values appearing only a few times in the database). The resulted access plan using hash join between two index range scans usually works quite well. Columns with low selectivity (few distinct values, each value appearing many times in the database) make no sense to be indexed on their own, the query optimizer will simply ignore them. However, low selectivity columns many times make good composite keys when they are paired with a high selectivity column.

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
              END
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
              END
WHERE DeviceID = 10 
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

UPDATE da
SET da.Details = upd.Details
FROM DeviceAttribute AS da
  JOIN
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name 
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;

Best Answer

Related Solutions

Sql-server – Should I use many single field indexes, instead of specific multi column indexes

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

Related Question