CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Sam P
Sam P

Reputation: 515

Shorter query approach

Both source and destination tables have about 40 columns. No. of rows to be updated is about 20. Row count of Destination table is around 30k.

Is there a better (shorter query) approach to below?

UPDATE destination_table t1
SET 
field1 = (select field1 from src_table t2 where t2.pk = t1.pk),
field2 = (select field2 from src_table t2 where t2.pk = t1.pk),
... 
field40 = (select field40 from src_table t2 where t2.pk = t1.pk),
WHERE EXISTS (select 1 from src_table t2 where t2.pk = t1.pk)

Upvotes: 2

Views: 80

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

Your original query is the Standard SQL-92 'scalar subquery' approach**. One reason to prefer it (other than notions of portability) is that SQL Server's proprietary UPDATE..FROM..JOIN syntax is gives potentially ambiguous and unexpected results.

However, the 'scalar subquery' approach is unquestionally verbose (although a smart optimizer will spot the repeated code). As a reault, the SQL-99 Standard introduced the MERGE statement, which was implemented (with useful extensions) in SQL Server 2008 e.g. 

MERGE INTO destination_table
   USING src_table t2
      ON t2.pk = destination_table.pk
WHEN MATCHED THEN
   UPDATE 
      SET field1 = T2.field1, 
          field2 = T2.field2, 
          field2 = T2.field3, 
          ...
          field40 = T2.field40;

As you are not supporting legacy (pre-2008) code and assuming you have no immediate plans to port to a SQL product that does not support MERGE (noting that Oracle and postgreSQL do and mySQL does not), I see no reason to use any construct other than MERGE for this kind of update.

** well, almost. Your update targets a table with a correlation name, 't1`. In SQL-92, the correlation name should have the effect of materializing a table that goes out of scope at the end of the SQL statement. In other words, although the syntax is valid, the result should be that the data in the underlying base table remains unaffected. However, it seems clear this is not the intention of your update and indeed the actual effect in SQL Server is that the base tables are affected, technically non-compliant behaviour!

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

update t1 set
    field1 = t2.field1,
    field2 = t2.field2,
    ... 
    field40 = t2.field40,
from destination_table t1
join src_table t2 on t2.pk = t1.pk

Added (after comment):

In my opinion it's better to avoid dynamic queries but if you really want you can do it the way bellow. Query is generated and there is no need to write down all columns, but all columns will be updated.

declare @sql as nvarchar(max)
set @sql = ''

select @sql = @sql + ', ' + column_name + ' = t2.' + column_name
from information_schema.columns
where table_name = 'destination_table'

set @sql =
    'update t1 set ' + 
    stuff(@sql, 1, 2, '') +
    ' from destination_table t1 join src_table t2 on t2.pk = t1.pk'

exec sp_executesql @sql

Upvotes: 3

Kev Ritchie
Kev Ritchie

Reputation: 1647

You could use something like this:

UPDATE      dest
SET         dest.Field1 = src.Field1,
            dest.Field2 = src.Field2,
            dest.Field3 = src.Field3,
            dest.Field4 = src.Field4,
            dest.Field5 = src.Field5,
            dest.Field6 = src.Field6
FROM        destination_table dest
INNER JOIN  src_table src
ON          src.pk = dest.pk

You just need to add in the extra fields to update.

Upvotes: 5

Related Questions