MYSQL insert only if values different

Question

I'm trying to insert a new row with the same ID into a simple table but only want to insert if the values are different.

This table is to track price history of an item. The table has the following columns:

id, timestamp, productID, price

I only want to insert a new record if either the product doesn't exist or the product does exist but the price has changed.

Unfortunately I'm having a brain block due to my limited knowledge and would appreciate help in where to turn so I don't have any trials at the code to do this.

Thanks!

I presume you refer to dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html but that would update, not insert a new row. I want a new row. — mika
– mika, Commented Jul 27, 2017 at 16:48

Iłya Bursov · Accepted Answer · 2017-07-27 16:58:59Z

2

you can try something like this:

SET @PRODUCT = 1; # product id
SET @PRICE = 1; # new product price

insert into `t`(`product`, `timestamp`, `price`)
select v.product, now(), v.price
from
(select @PRODUCT as `product`, @PRICE as `price`) as v
left outer join
(select `product`, `price` from `t` where `product`=@PRODUCT order by `id` desc limit 1) as p
on (v.product=p.product)
where
(p.price is null) or
(p.price <> v.price);

so, this statement either insert new row (for new product or new price) or does nothing

answered Jul 27, 2017 at 16:58

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

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

4 Comments

mika Over a year ago

This works perfectly and solves the issues you brought up in your comment earlier with the possibility the price changes back to a previous value. Thanks much!

mika Over a year ago

It seems as there's some kind of issue when the product ID is alphanumeric it returns an unknown column error. I'm trying to figure that out but wanted to also provide feedback to you as well. Ex: SET @PRODUCT = P017428772; # product id

Iłya Bursov Over a year ago

@mika you must be kidding me, of course you should enclose strings into single quotes, like SET @PRODUCT = 'P017428772'; if you don't know that - I suggest you to stop anything you're doing and open any sql book for beginners

mika Over a year ago

Yes, I'm a noob. Sorry. I can do basic stuff but this query was much more complicated than what I am used to so I just missed that. I do appreciate your help!

Arun pandian M · Accepted Answer · 2017-07-27 16:40:04Z

-1

u need composite primary key

ALTER TABLE products ADD PRIMARY KEY(product,price);

after this query if you insert if the product and price is same in your table returns error with duplicate entry

or it will insert the query even one field value changes

answered Jul 27, 2017 at 16:40

Arun pandian M

89210 silver badges17 bronze badges

3 Comments

Iłya Bursov Over a year ago

it will not work, for example price was 1 then changed to 2 and then again back to 1

Arun pandian M Over a year ago

there no such words in the op post he just said "the product does exist but the price has changed. " and @IlyaBursov and he want to store history like price changed 1 today tommorrow it will be 2 just like that my answer is right according to my concern

Iłya Bursov Over a year ago

This table is to track price history of an item track history means that you want to store all price changes

Collectives™ on Stack Overflow

MYSQL insert only if values different

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related