Efficient of SQL Update Query

Question

How to make this syntax below more efficient because i have to update more than 20 fields for one record?

 UPDATE TRANS SET A = @XA WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and A <> @XA
 UPDATE TRANS SET B = @XB WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and B <> @XB 
 UPDATE TRANS SET C = @XC WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and C <> @XC 
 UPDATE TRANS SET D = @XD WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and D <> @XD 
 UPDATE TRANS SET E = @XE WHERE UNIQX = @XUNIQX AND STS_TRANS = 0 and E <> @XE

score 1 · Answer 1 · answered Mar 22 '18 at 18:45

1

You could combine it:

UPDATE TRANS 
SET A = @XA
   ,B = @XB
   ,C = @XC
   ,D = @XD
   ,E = @XE
WHERE UNIQX = @XUNIQX 
  AND STS_TRANS = 0;

Even if A is not different @XA there will be A -> A(identity).

Please also note that A <> @XA could be tricky if column A is nullable: what is “=null” and “ IS NULL”

answered Mar 22 '18 at 18:45

Lukasz Szozda

162,964
23
234
275

but it still update the field A. Field A get update if all of conditions are match otherwise ignore it. Merge Replication reason. any idea? btw, thanks for reply. – magniche Mar 22 '18 at 19:16

Daniel Marcus · Accepted Answer · 2018-03-22T19:01:17.540

1

Use a loop and dynamic sql like this:

    create table #temp (idx int identity, column_name varchar(max))
    insert #temp
    select column_name from INFORMATION_SCHEMA.columns
    where table_name='trans' --and filter columns you want here

    declare @XUNIQX varchar(max) --set value here
    declare @itrerator int = 1
    declare @columnname varchar(max)
    while @itrerator<=(select max(idx) from #temp)
begin
    select @columnname=column_name from #temp where idx=@iterator
    exec ('UPDATE TRANS SET '+@columnname+' = @X'+@columnname+' WHERE UNIQX ='+ @XUNIQX+' AND STS_TRANS = 0 and '+@columnname+' <> @X'+@columnname+'')
set @iterator=@iterator+1
    end

edited Mar 22 '18 at 19:01

answered Mar 22 '18 at 18:56

Daniel Marcus

2,686
1
7
13

it's working correctly but this is the same way with my code. the different is only manual typing or auto generate the name of fields. Any ideas? thanks for reply – magniche Mar 22 '18 at 19:38
This way the code will loop through each update regardless of how many columns you want to update – Daniel Marcus Mar 22 '18 at 19:44
I have an idea base on your code. Thanks for helping. – magniche Mar 22 '18 at 19:47

Efficient of SQL Update Query

2 Answers2