mysql - INSERT... ON DUPLICATE KEY UPDATE, but not really...?

Question

Hi I've been trying to get this to work, I thought I had it with mysql - INSERT... ON DUPLICATE KEY UPDATE, but no luck.

I have a table as such:

sessionID is unique, productID references another table and is not unique, but not common, should be a max of 3 rows containing the same value, sessionType is either 1, 2 or 3, and would link with productID,

I need to check if the table has a row where there is a matching pair of productID and sessionType, if there is then sessionDate & sessionCapacity in that row should be UPDATED, if there isn't then a new row inserted.

$vals = array($pID,$data['pSessionDate'],'1',$data['pQty'],$pID,$data['pSessionDate'],'1',$data['pQty']);
$db->Execute("INSERT INTO VividStoreSessions (pID,sDate,sType,sCapacity) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE pID=?,sDate=?,sType=?,sCapacity=?",$vals);

Hope that makes sense to someone and thanks in advance for any help!

dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html we have this in mysql version 5.0 and above — Narendra Yadav
– Narendra Yadav, Commented Jan 10, 2016 at 20:41

Gordon Linoff · Accepted Answer · 2016-01-10 20:41:04Z

2

Your insert looks valid. But, first you need a unique index/constraint:

create unique index unq_VividStoreSessions_productId_sessionType
    on VividStoreSessions, productId, sessionType)

Then you can write the code to only use four parameters:

INSERT INTO VividStoreSessions (pID, sDate, sType, sCapacity)
     VALUES (? ,?, ?, ?)
    ON DUPLICATE KEY UPDATE sDate = VALUES(sDate), Capacity = VALUES(Capacity);

Finally, you need to ensure that sType only takes on the values of 1, 2, or 3. Perhaps you want to enforce this at the application layer. Otherwise, you need a trigger or foreign key constraint to ensure that there are only three rows.

answered Jan 10, 2016 at 20:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

mysql - INSERT... ON DUPLICATE KEY UPDATE, but not really...?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related