CODEWITHSUNDEEP
sqlsql-server
Nuru Salihu
Nuru Salihu

Reputation: 4928

What is the difference between these two queries pls?

When updating a table from another or performing query that involves comparing two tables, I normally have a habit of querying like below

UPDATE dbo.Table1 SET COL =
(
 SELECT i.colid FROM 
    dbo.Table2 i 
      WHERE i.did = dbo.Table1.did and 
         i.[pdate] = dbo.Table1.pdate and
         i.pamount = dbo.Table1.pay and 
         i.[Ref No ] = dbo.Table1.refno and
         i.[cNo ] = dbo.Table1.receipt
)

AND recently the above failed me. It returns an error saying query returns morethan 1 record/row. However My superior queried like below and it works . I still cant figure out why mine failed and his succeed. And why it never failed me before ?

UPDATE dbo.Table1 SET COL = i.colid 
from
 dbo.Table2 i 
   WHERE i.did = dbo.Table1.did and 
         i.[pdate] = dbo.Table1.pdate and
         i.pamount = dbo.Table1.pay and 
         i.[Ref No ] = dbo.Table1.refno and
         i.[cNo ] = dbo.Table1.receipt

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Your query fails because it returns more than one row. That is, there are two or more rows that match the conditions. I think this is pretty obvious from the error. You have a scalar subquery. It should return at most one row and one column.

The second works, because joins are allowed to have multiple rows matching. However, only one of the values is set set -- from one of the arbitrary matching rows. In my opinion, it is bad form to have multiple multiple matching rows match and have an arbitrary value chosen. But, it does work.

Upvotes: 3

Related Questions