INSERT IGNORE vs UPDATE IGNORE

Databases
1

I have two tables: ‘Store’ and’ Product’. To connect the two I have a lookup table called prices.

When I add a new store to the ‘store’ table or product to the ‘product’ table, I run the following sql command to add the new store/product to the lookup table.

INSERT IGNORE INTO prices (sid, pid)
SELECT store.id, product.id
FROM store
JOIN product

This works very well. However, when I delete a store or product I want to UPDATE the lookup table. I tried what I thought was obvious:

UPDATE IGNORE prices (sid, pid)
SELECT store.id, product.id
FROM store
JOIN product

But it doesn’t work at all. I’ve been searching but I haven’t found anything that works.

I realize I could just run a DELETE command and do it that way but I would need to enter in the missing ID from one of the tables and I might enter the wrong ID.I don’t want to run the risk of deleting the wrong PID or SID and lose the information associated with the store/product match up. I want the database query to find the differences and then delete the proper row(s)

Any ideas?

Thanks
Steve

2

foreign keys with ON DELETE CASCADE

3

ON DUPLICATE KEY UPDATE is not really in the standard. It’s about as standard as REPLACE is.
Essentially both commands are alternative-syntax versions of standard commands.

4

Thanks for trying to help. I appreciate it. After a while longer of searching I found something similar to what I wanted and then modified it. It works perfectly. Tested several times with different scenarios and it worked in all. Here it is for further reference.

DELETE FROM prices

WHERE pid NOT IN (SELECT id FROM product) 
OR sid NOT IN (SELECT id FROM store)
5

did you even look at ON DELETE CASCADE?

1 Like
9

Is that right? And just what is this “standard” you refer to? Please do cite where this is an “alternative syntax”. Alternative to what?

According to the documentation:

INSERT … ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.

10
11

Thanks @r937. I am aware of of that. I was interested in what @simptiongst was referring to. It didn’t sound like he was referring to actual SQL Standards. I was more interested in what he was going to say was the “standard” that ON DUPLICATE KEY UPDATE was an “alternative” of.

EDIT* Any reason you linked to SQL-92 and not SQL:2011?

12

laziness

1 Like
13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.