Gabscap Gabscap - 1 year ago 256
MySQL Question

MySQL INSERT INTO ... SELECT or default value

I'm working on following query:

INSERT INTO a (id, value) VALUES (_id, (SELECT value FROM b WHERE = _id));

Table a: id, value (has a default value)

Table b: id, value

Table b does not contain all requested values. So the SELECT query sometimes returns 0 rows. In this case it should use the default value of a.value.

Is this somehow possible?


Solution for empty columns in comments of the post marked as solved.

Answer Source

The following query would work. First the max(value) is looked up from table b for _id. It would be either NULL or equal to b.value. If it is NULL (checked using the COALESCE function), then the default value of the value column of table a is set as the value.

The default value of the value column of table a is accessed using the DEFAULT function (please refer Reference 1).

  COALESCE(max(value), (SELECT DEFAULT(value) FROM a LIMIT 1)) value
WHERE id = _id;

SQL Fiddle demo


  1. How to SELECT DEFAULT value of a field on SO
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download