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.

Answer 1

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.