if "Subquery returns more than 1 row" consider it NULL

Question

I'm trying to sync store ids on newtable with the ids from the maintable here:

UPDATE newtable t SET t.store_id = (SELECT store_id FROM maintable s 
WHERE t.state = s.state AND s.city = t.city AND t.name = s.name)

Whenever a subquery returns more than one row it errors out with "Subquery returns more than 1 row", but when it returns zero rows the subquery is considered to have returned nothing so the store_id on newtable remains NULL. Nothing new here, it's just how it works.

I'd like to know if it's possible to let the subquery output the same as what it does when it has no matches when it has more than one matching row.

This way I'd get the store_id synced only for ONE matching row on the main table and skipped when more than one matching row comes out in the subquery.

If the `SELECT store_id` yields one value then set `t.store_id` to that value; if it yields nothing then set `t.store_id` to NULL; if it yields more than one value then set `t.store_id` to NULL. Right? — mu is too short, Jul 13 '11 at 02:50
@mu is too short: I think when more than one value is returned, he just wants to discard the duplicate values and store only one of them in `store_id`, and when nothing is returned, he wants to store a default value. — MD Sayem Ahmed, Jul 13 '11 at 02:54
@Sayem: That's the one that's unclear to me, the last two paragraphs in the question seem to be saying different things. — mu is too short, Jul 13 '11 at 02:57

score 7 · Accepted Answer · answered Jul 13 '11 at 03:16

I think you might be looking for a HAVING clause to force the query to match exactly once:

UPDATE newtable t
SET t.store_id = (
    SELECT store_id
    FROM maintable s
    WHERE t.state = s.state
      AND s.city  = t.city
      AND t.name = s.name
    HAVING COUNT(*) = 1
)

That should make multiple matches behave the same as no matches. The HAVING clause is applied almost at the very end of the query process; if there are no matches from the WHERE or more than one match, then COUNT(*) = 1 will fail and the inner query will return nothing but if there is exactly one row then COUNT(*) = 1 will succeed and the inner query will return that single match.

Is there anything similar to this in MSSQL? – Ganesh M S Apr 06 '22 at 23:58 — Ganesh M S, Apr 06 '22 at 23:58
@GaneshMS Sorry, I know very little about SQL Server. – mu is too short Apr 07 '22 at 04:13 — mu is too short, Apr 07 '22 at 04:13

score 0 · Answer 2 · answered Jul 13 '11 at 02:49

0

You might consider putting a LIMIT 1 in your sub-query to better achieve what you are trying to accomplish, depending on your specific needs.

Otherwise, you should be able to get creative with IF or CASE:

UPDATE newtable t SET t.store_id = (
    SELECT IF(num>1, NULL, storeid) FROM (
        SELECT COUNT(*) AS num, storeid FROM maintable s WHERE t.state=s.state AND s.city=t.city AND t.name=s.name
    )
)

Untested, but should get you in the ballpark.

answered Jul 13 '11 at 02:49

Brad

159,648
54
349
530

LIMIT 1 isn't an option because it wouldn't be an exact store_id match. I'll play with IF, CASE and let you know. – Caio Iglesias Jul 13 '11 at 03:13

Seeker · Answer 3 · 2011-07-13T03:45:01.090

0

UPDATE newtable t SET t.store_id = IFNULL((SELECT store_id FROM maintable s 
WHERE t.state = s.state AND s.city = t.city AND t.name = s.name HAVING COUNT(*) = 1), t.store_id)

IFNULL(use_this_value_if_not_null,value_if_first_isnull)

edited Jul 13 '11 at 03:45

answered Jul 13 '11 at 02:54

Seeker

365
1
6
16

I've added the HAVING COUNT(*) = 1 like the "@mu is too short", just to have a complete solution because I've not sure if the @mu answer works when the inner query returns null for what you want. – Seeker Jul 13 '11 at 03:47

if "Subquery returns more than 1 row" consider it NULL

3 Answers3