MySQL Insert a different value depending on a condition

Question

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)?

criticalfix · Accepted Answer · 2013-09-10 15:53:22Z

2

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

answered Sep 10, 2013 at 15:53

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CookieEater Over a year ago

Thanks, I did not know about COALESCE().

Praveen Lobo · Accepted Answer · 2013-09-10 15:59:08Z

1

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.

edited Sep 10, 2013 at 15:59

answered Sep 10, 2013 at 15:51

Praveen Lobo

7,2172 gold badges31 silver badges44 bronze badges

Collectives™ on Stack Overflow

MySQL Insert a different value depending on a condition

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related