Gabscap Gabscap - 2 months ago 16
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 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?

Edit:

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

Answer

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