Update mysql row if value different or create new row not added yet

Question

For some reason this is not coming to me, and I do not see this specific question answered where there are two conditions. I want to update my_table for the following two conditions:

If the Primary key vin is the same and the price is different. - or -
If there is no vin then just create a new row

Example of before and after two example updates:

In this exable vin 12345 exist so just the price is update as it's different, in the other example vin 55555 wasn't in the table so it's created.

"and I do not see this specific question answered where there are two conditions" - that's because there aren't, actually ... apart from that updating the existing row with the same price it already had wouldn't do too much harm (unless there's other restrictions you failed to mention), it won't actually happen, because the DBMS is smart enough to recognize that - see what the documentation has to say about the affected-rows value ... — C3roe
– C3roe, Commented Dec 6, 2017 at 22:10

agim · Accepted Answer · 2017-12-06 22:08:37Z

2

MySql supports a conditional insert/update - check https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

INSERT INTO my_table (vin,price) VALUES (12345, 7777)
    ON DUPLICATE KEY UPDATE price=7777;

answered Dec 6, 2017 at 22:08

agim

1,84113 silver badges19 bronze badges

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

3 Comments

Mike Q Over a year ago

My issue is that I only want to update if the value is different than server value, in your case it is saying just update it with 7777 no matter what value it was in the DB

C3roe Over a year ago

@MikeQ and what actual harm do you think that would do? And again - it won't actually happen. The DBMS will recognize that a row already has the values this statement tries to "update" it to, so it will just go, "we're alright here matey, nothing to do, let's move on to the next one" ... so what actual problem do you imagine needs solving here ...?

Mike Q Over a year ago

Say price = 1234 and on the DB it's 5678, in your case I would update the DB with 1234 100% of the time.... crap I think I realize now my stupidness ... yeah I am doing the wrong thing here, your answer is correct, I think I need a date component to keep the check from happening all of the time .. sorry for wasting people's time

Collectives™ on Stack Overflow

Update mysql row if value different or create new row not added yet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related