How to bypass the 'Subquery returned more than 1 value' error?

Question

question

David 2 1 asked Jan 21, '10

How can I get around this subquery error returned from the below sql? For each NUM

update tab1
set text = (select tab2.text
from tab2, tab1
where tab1.num = tab2.num)

Tab2 contains data like:
num text
123 abc
345 def
345 def
678 ghi
678 ghi

Thanks in advance.

t-sql sql-server-2000 update

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-21T09:34:43Z

RickD answered Jan 21, '10

As a quick and simple answer:

   update tab1            
    set text = (select DISTINCT tab2.text            
    from tab2, tab1            
    where tab1.num = tab2.num)

As a better answer, get rid of your sub query:

   update tab1            
    set text = tab2.text            
    from tab1            
    JOIN tab2            
    ON tab2.num = tab1.num

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.