CODEWITHSUNDEEP
mysqlsqlselectinsert
Gabscap
Gabscap

Reputation: 287

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?

Upvotes: 3

Views: 5619

Answers (3)

Albert Gevorgyan
Albert Gevorgyan

Reputation: 191

you can wrap the value in coalesce(max(value), default_value)

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

Upvotes: 3

Joseph B
Joseph B

Reputation: 5669

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

Upvotes: 0

MatBailie
MatBailie

Reputation: 86775

If MySQL follows other RDBMS behaviour, the default is only picked up when you don't even specify the field. This means that you need two different INSERT statements:

IF (EXISTS(SELECT * FROM b WHERE id = _id)) THEN
  INSERT INTO a (id, value) SELECT _id, value FROM b WHERE id = _id;
ELSE
  INSERT INTO a (id) SELECT _id;
END IF;

Or, possibly, something like this...

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

IF ((SELECT ROW_COUNT()) = 0) THEN
  INSERT INTO a (id) SELECT _id;
END IF;

Please note, this is conceptual. I've looked up the syntax for you, but I haven't tested it on MySQL.

Upvotes: 0

Related Questions