sql - What is the difference between these two queries pls? -

when updating table or performing query involves comparing 2 tables, have habit of querying below

update dbo.table1 set col = (  select i.colid      dbo.table2        i.did = dbo.table1.did ,           i.[pdate] = dbo.table1.pdate ,          i.pamount = dbo.table1.pay ,           i.[ref no ] = dbo.table1.refno ,          i.[cno ] = dbo.table1.receipt ) 

and above failed me. returns error saying query returns morethan 1 record/row. superior queried below , works . still cant figure out why mine failed , succeed. , why never failed me before ?

update dbo.table1 set col = i.colid   dbo.table2     i.did = dbo.table1.did ,           i.[pdate] = dbo.table1.pdate ,          i.pamount = dbo.table1.pay ,           i.[ref no ] = dbo.table1.refno ,          i.[cno ] = dbo.table1.receipt 

your query fails because returns more 1 row. is, there 2 or more rows match conditions. think pretty obvious error. have scalar subquery. should return @ 1 row , 1 column.

the second works, because joins allowed have multiple rows matching. however, 1 of values set set -- 1 of arbitrary matching rows. in opinion, bad form have multiple multiple matching rows match , have arbitrary value chosen. but, work.