MySQL: handle EMPTY SET in UPDATE statement

Question

I have: something like

UPDATE table
SET field = (SELECT field FROM another_table WHERE id = @id);

Problem: SELECT field FROM another_table WHERE id = @id subquery can return one field or EMPTY SET.

Question: How to handle situation when subquery returns empty set?

Updated:

UPDATE table t
SET field = IF((SELECT field FROM another_table WHERE id = @id) IS NOT NULL, -- select field
  (SELECT field FROM another_table WHERE id = @id), -- Problem #1: select field AGAIN!
  (SELECT field FROM table WHERE id = t.id) -- Problem #2: try to not change value, so select the current field value!!
);

c3cris · Accepted Answer · 2014-06-04 18:39:16Z

1

If function can be useful:

UPDATE table
SET field = if((SELECT field FROM another_table WHERE id = @id) IS NULL,true,false);

answered Jun 4, 2014 at 18:39

c3cris

1,3263 gold badges16 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VB_ Over a year ago

I have IF((SELECT field FROM another_table WHERE id = @id) IS NOT NULL, true_expr, false_expr). Problem: Actually my true_expr equals to (SELECT field FROM another_table WHERE id = @id). Question: How to avoid selecting the sme value twice?

c3cris Over a year ago

If both ID's are unique it should be ok. If not elaborate on your scenario.

VB_ Over a year ago

look at Updated section of my question please

Sam · Accepted Answer · 2014-06-04 18:44:57Z

You can add the conditional:

WHERE (SELECT COUNT(*) FROM another_table WHERE id = @id) > 0

This will make sure that at least one row exists in another_table with the id. See my SQL Fiddle as an example.

Note: this may not be the most efficient because it does a count on another_table, and if it is greater than 1 it will do another SELECT (two sub-queries). Instead, you can do an INNER JOIN:

UPDATE table
INNER JOIN another_table ON table.id=another_table.id
SET table.field = another_table.field
WHERE another_table.id = @id;

See this SQL Fiddle. The reason why I saved this as a second option, is not all SQL languages can UPDATE with joins (MySQL can). Also, you need some way to relate the tables..in this case I said that the table.id we are updating is equal to another_table.id we are taking the data from.

spencer7593 · Accepted Answer · 2014-06-04 18:53:32Z

1

NOTE The UPDATE statement will modify EVERY row in table and assign the same value to every row; that seems a little unusual.

To answer your question:

If you want to handle the "empty set" by not updating any rows in table, then one way to do this is with a JOIN to an inline view:

UPDATE table t
 CROSS
  JOIN (SELECT a.field 
          FROM another_table a
         WHERE a.id = @id
         LIMIT 1
       ) s
   SET t.field = s.field

Note that if the inline view query (aliased as s) return an "empty set", then no rows in table will be updated, because the JOIN operation will also return an "empty set", meaning there are zero rows to be updated.

answered Jun 4, 2014 at 18:53

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Collectives™ on Stack Overflow

MySQL: handle EMPTY SET in UPDATE statement

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related