mysqlsqlselectinsert

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 b.id = _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

  • 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).

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

    SQL Fiddle demo

    Reference:

    1. How to SELECT DEFAULT value of a field on SO