0

I have a table structure like this:

  • id (primary) bigint
  • attributes (unique) varchar (comma separated numbers)
  • product_id bigint
  • price text

If attributes and product id match and therefore the row already exists then I just want to update the price, otherwise, create a new row.

I have this code which is part of a loop, it will send multiple INSERT INTO queries containing different attributes, product ids and prices, right now it can't determine if the attributes and product id match and appears to only update the price on the attributes column match:

INSERT INTO my_table (id,attributes,product_id,price) VALUES ('','61','4000','500') ON DUPLICATE KEY UPDATEprice= '500'

I have attempted to define unique on both the attributes and product id columns but when doing so it updates the price on an existing row matching the attributes only (which when the product id isn't matched means the prices gets added to the wrong row) + new rows not created.

Maybe the answer is a different structure. How can I do this?

Sayed Mohd Ali
  • 2,156
  • 3
  • 12
  • 28
bigdaveygeorge
  • 947
  • 2
  • 12
  • 32
  • 1
    Create a composite `UNIQUE` constraint on `(attributes, product_id)`. You can also get rid of auto-increment `id` in that case. Also, it smells like bad design when you say "comma seperated numbers" for `attributes` column. Stop storing delimiter separated values; and instead normalize the DB. Read: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Madhur Bhaiya Jul 18 '19 at 12:24

1 Answers1

0

Try 

INSERT INTO my_table (attributes,product_id,price) VALUES ('61','4000','500') ON DUPLICATE KEY UPDATE price= '500'
colinam1992
  • 23
  • 7