CODEWITHSUNDEEP
sql
magniche
magniche

Reputation: 11

Efficient of SQL Update Query

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

Upvotes: 0

Views: 32

Answers (2)

Daniel Marcus
Daniel Marcus

Reputation: 2686

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

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”

Upvotes: 1

Related Questions