How to check for details of subquery output?

Question

I have a query like this:

UPDATE t3
SET    somevalue  = (SELECT t2.id
                  FROM   table1 t1
                         JOIN table2 t2
                           ON t1.fk_table2_id = t2.id
                  WHERE  t3.id = t1.fk_table3_id)
FROM   table3 t3

The subquery SELECT t2.id FROM table1 t1 JOIN table2 t2 ... returns 2+ values at some place in my SQL Server 20008 DB. Is there an easy way to figure out where it fails? Or is there another way to update a column in one table with values from another?

Thanks in advance

You could try using TOP 1 in your subquery. Obviously it looks like there is some inconsistent data, at least as per your thinking. So if you're happy to ignore that then top 1 should fix your immediate problem — Shiv Kumar
Yeah, I already tried top 1 on the subquery, problem is, I am not sure if the results are correct, so I wanted to see where it fails to check... — grady

The Scrum Meister The Scrum Meister · Accepted Answer · 2011-02-18T08:16:05

Assuming ts.id is unique, you can find out where its returning more then 1 row, use a SELECT (omit the table3 from the query if t1.fk_table3_id really is a foreign key constraint):

SELECT t3.id, COUNT(*)
FROM  table1 t1 JOIN table2 t2 ON t1.fk_table2_id = t2.id
  JOIN table3 t3 ON t3.id = t1.fk_table3_id
GROUP BY t3.id
HAVING COUNT(*) > 1

How to check for details of subquery output?

1 Answers