CODEWITHSUNDEEP
mysql
CookieEater
CookieEater

Reputation: 2496

MySQL Insert a different value depending on a condition

I have the following query in MySQL. This inserts a new item to a list where the position of the new item is set to 1 greater than the current maximum position.

INSERT INTO items
SET item_name="apple",
    position=(SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12),
    list_id=12

This works, but for position, I want to insert a 0 if the SELECT statement returns an empty set. Is this possible to do in one query (so that I can avoid using transactions)?

Upvotes: 0

Views: 62

Answers (2)

Praveen Lobo
Praveen Lobo

Reputation: 7187

use CASE to check if you have any position at all by doing IS NULL. If you don't, just use 0. 

INSERT INTO items
SET item_name="apple",
    position=( CASE WHEN (SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12) IS NULL 
                THEN 0 ELSE (SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12) END ) ,
    list_id=12

See demo

Though this works, see @criticalfix's better answer and a demo for both here.

Upvotes: 1

criticalfix
criticalfix

Reputation: 2870

Try COALESCE():

INSERT INTO items
SET item_name="apple",
position=COALESCE((SELECT MAX(i.position)+1 FROM items i WHERE i.list_id=12), 0),
list_id=12

Upvotes: 2

Related Questions