MySQL - Insert values if not exists or update and return id

Question

I have a table which looks f.ex. like this:

id (primaryKey, auto_increment) | fruit | color

I would like to insert values for fruit and color, if the fruit value does not exist, else I would like to update just the color and get back the id of the inserted or updated row.

F.ex. if I have a row with:

1234 | apple | red

and want to update the color of the apple to green, without knowing that there is already a row containing an apple. I use this code:

$sqli = get_db();
$q1 = "INSERT INTO table (fruit, color) VALUES ('apple', 'green') ON DUPLICATE KEY UPDATE color='green', id=LAST_INSERT_ID(id)";
$r1 = $sqli->query($q1);
$insertedOrUpdatedID = $sqli->insert_id();

I want to update the existing row to:

1234 | apple | green

and get back the id (1234) in $insertedOrUpdatedID.

I think I need to tag the fruit column in any way. When I'm executing this code, it always creates a new row (1235|apple|green) without updating the existing one or returning the edited id.

SOLUTION:

Changing the type of 'fruit' from text to varchar(100) and setting its KEY to UNIQUE solves the problem. Moreover change the last line of the code to:

$insertedOrUpdatedID = mysqli_insert_id( $sqli );

in order to get the right ID.

Happy coding!

UNIQUE constraint to fruit attribute may prevent inserting duplicate rows. — soonoo
– soonoo, Commented Dec 23, 2015 at 17:07
@RiggsFolly Thanks, but unfortunately when I am executing REPLACE INTO table (fruit, color) VALUES ('apple', 'green') it still creates a second row — user3191334
– user3191334, Commented Dec 23, 2015 at 17:25
@soonoo Thanks, but when I am trying to change fruit to UNIQUE, MySQL says "BLOB/TEXT column 'fruit' used in key specifications without a key length", also when I set the length to 100 — user3191334
– user3191334, Commented Dec 23, 2015 at 17:27
@soonoo **EDIT: - changing it to varchar(100) and then to UNIQUE did the trick and it WORKS! Thank you!! ** — user3191334
– user3191334, Commented Dec 23, 2015 at 17:33

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

If you want to update by fruit name so create fruite as UNIQUE KEY

Try this query

REPLACE INTO table_name (fruit,color) VALUES ('apple','green');

OR

INSERT INTO table_name (fruit,color) VALUES ('apple','green') ON DUPLICATE KEY UPDATE color = 'green;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 23, 2015 at 17:25

Vipin Jain

3,77618 silver badges36 bronze badges

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

2 Comments

user3191334 Over a year ago

THANKS! And also thanks to @soonoo!! The trick was to change the 'fruit' type from text to varchar(100) and then make it UNIQUE ! Now the insert query works!

Vipin Jain Over a year ago

Welcome friend... It's my pleasure :)

Collectives™ on Stack Overflow

MySQL - Insert values if not exists or update and return id

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related