SQL update select statement

Question

I'm trying to update an entry in my database (postgresql). I'm having a problem with updating the query:

INSERT INTO prices (price, product_id)
SELECT 
product_price, commn_id
FROM products_temp as prod
WHERE NOT EXISTS 
(SELECT *
   FROM   prices od
   WHERE  prod.commn_id = od.product_id)
OR prod.product_price != 
( SELECT price
  FROM prices as p
  WHERE p.product_id = prod.commn_id
  order by p.end
  desc LIMIT 1 )

Query works fine when i delete:

OR prod.product_price != 
( SELECT price
  FROM prices as p
  WHERE p.product_id = prod.commn_id
  order by p.end
  desc LIMIT 1 )

So it seems to me that it's looping through this operation. My question is, how can I fix it?

What does work fine mean What is happening when it is not "working fine"? — Gordon Linoff, Nov 19 '18 at 16:27
I mean that query insert new prices. When it's not working, the query is looping itself and nothing happens except infinite execution. — Michal, Nov 19 '18 at 16:58
INSERT's should not have infinite execution unless the subquery selects a massive amounts of records or is run on a large table without indexes then it can take some time indeed to execute. — Raymond Nijland, Nov 19 '18 at 17:10
`NOT EXISTS (SELECT * FROM prices od WHERE prod.commn_id = od.product_id)` is a bit wierd.. `NOT EXIST` are SQL boolean "short circuiting" keywords better is to give it boolean valus like 1 or 0 so your better off using `(SELECT 1 FROM prices od WHERE prod.commn_id = od.product_id)` instead.. — Raymond Nijland, Nov 19 '18 at 17:13
Side note: You're almost certainly going to be better off doing this [as an `UPSERT`](https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql), which is almost certainly going to be cleaner/faster. My guess is that your actual problem is the additional, optional, condition is causing the system to throw unique-constraint violations (on `prod.commn_id`), but you haven't provided enough details to be sure. — Clockwork-Muse, Nov 19 '18 at 18:38

score 0 · Answer 1 · answered Nov 19 '18 at 18:22

I think your whole problem comes from the operator in prod.product_price !=
I tried this and it worked for me :

INSERT INTO prices (price, product_id)
SELECT 
product_price, commn_id
FROM products_temp as prod
WHERE NOT EXISTS 
(SELECT *
   FROM   prices od
   WHERE  prod.commn_id = od.product_id)
OR prod.product_price not in 
( SELECT price
  FROM prices as p
  WHERE p.product_id = prod.commn_id
  order by p.end
  desc LIMIT 1 )

Hope it will help you !

SQL update select statement

1 Answers1