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?
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;
Reference: