CODEWITHSUNDEEP
sqlsql-server
TheProgrammer
TheProgrammer

Reputation: 1344

Is there a more efficient query I can use

I want to know if theres a better way to script out this update statement:

update table A
set item = (select itemA + ' - ' + itemB from table B where id = 1),
temp = (select itemA from table B where id = 1)
where sid = (select sItemId from table B where id = 1)

Upvotes: 2

Views: 37

Answers (3)

P. Kouvarakis
P. Kouvarakis

Reputation: 1943

SQL Server specific syntax:

update a
set item = b.itemA + ' - ' + b.itemB,
    temp = b.itemA
from tableA a
     inner join tableB b on a.sid = b.sItemId
where b.id=1

Alternatively you can use MERGE:

merge into tableA as a
using tableB as b
on a.sid = b.sItemId and b.Id = 1
when matched then update set item = b.itemA + ' - ' + b.itemB, temp = b.itemA
;

The advantage of merge is that it is standard SQL syntax, and it will fail if an attempt is made to update the same record twice (the update with join will silently do only one of the updates). IIRC in later versions (with the respective compatibility level) update will fail as well. Also merge is more flexible.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

Use JOINS to make it more efficient and Alias to make it more readable. 

UPDATE A
SET A.item  = B.itemA + ' - ' + B.itemB 
   ,A.temp  = B.itemA 
FROM tableA A 
INNER JOIN tableB B ON A.[sid] = B.sItemId 
where B.id = 1

Upvotes: 4

Ashkan S
Ashkan S

Reputation: 11471

It is of course better to use only one join instead of retrieving the same table (tableB) 3 times

UPDATE tableA 
SET tableA.item =tableB. itemA + ' - ' + tableB.itemB, 
tableA.temp = tableB.itemA ,
...
FROM tableA, tableB
WHERE tableA.sid= tableB.sItemId And tableB.id =1

Upvotes: 0

Related Questions