logo
Tags down

shadow

mysql insert ignore vs on duplicate key


By : William Sutter
Date : May 05 2020, 08:21 AM

Let me , I have the following tables :

Case 1:

ID                Loc                In                    Out
----------------------------------------------------------------
null              null               null                  null

Working query for this case:

INSERT INTO Inventory VALUES( 'PA', 'Area 1', now(), NULL );

Case 2:

ID                 Loc                In                    Out
----------------------------------------------------------------
PA                 Area 1             2017-11-16 11:20:09   null

Working query for this case:

INSERT IGNORE INTO Inventory VALUES( 'PA', 'Area 1', now(), NULL );

Case 3:

ID                 Loc                In                    Out
----------------------------------------------------------------
PA                 Area 1             null                  2017-11-16 11:20:09

Working query for this case:

INSERT INTO Inventory VALUES( 'PA', 'Area 1', now(), NULL )
ON DUPLICATE KEY UPDATE In = now(), Out = NULL;

 I want combine the above 3 "Working mysql query" into one query?

Basically, the goal is to only execute case 3 if PK == Duplicate and In == NULL.

Thanks in advance :.

Answer :

INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE are mutually exclusive. They both handle the situation when the row to be inserted conflicts with an already existing row on an UNIQUE INDEX.

IGNORE converts errors into warnings and  lets the query succeed and ignore the new inserted values.

ON DUPLICATE KEY UPDATE updates some of the fields of the existing row with the new values.

Both clauses are used only if an UNIQUE INDEX conflict happens; they are ignored otherwise. This makes both of them work in your case #1

Basically, the goal is to only execute case 3 if PK == Duplicate and In == NULL.

A carefully crafted ON DUPLICATE KEY UPDATE clause can do the job:

INSERT INTO Inventory VALUES('PA', 'Area 1', NOW(), NULL)
ON DUPLICATE KEY UPDATE
    `In` = IF(ISNULL(`In`), VALUES(`In`), `In`),
    `Out` = NULL

The value of expression IF(ISNULL(`In`), VALUES(`In`), `In`) is VALUES(`In`) (the value of In in the VALUES() list to be inserted) if the current value of column In is NULL or the current value of column In if it is not NULL.

This way, the value of column In is replaced by the value provided by the INSERT query only if its current value is NULL.



Share : facebook icon twitter icon
Related Posts Related Posts :
  • clear array after form submit
  • pass url variable to javascript
  • access data using api
  • div position fixed css
  • add horizontal line between two div
  • disable toggle button css
  • css fixed line height
  • background image not working
  • table cell border
  • text line break css
  • overlay div on div
  • text direction rtl css
  • javascript convert string to an object
  • bind inner div click to outer div
  • jquery ajax oncomplete
  • how to use promise in for loop
  • jquery get element using variable
  • javascript send textbox value
  • pass div class content to another class or id
  • css new line after element
  • css calc not working properly
  • link disabled jquery
  • sql join table to subquery
  • MS Access SQL Issue with OR AND Operators
  • set textarea value using php
  • php merge two arrays into one
  • php form with google captcha
  • update current row in php
  • php date format mysql
  • mysql pdo select query
  • php function return an array
  • PHP mysqldump database to sql file
  • display image query
  • php create array using foreach loop
  • associative array php
  • group_concat in mysql laravel
  • php array_push empty
  • mysql on update cascade not working
  • pdo insert prepared statement
  • mysql insert into existing row
  • mysql multi insert query
  • mysql group by show all rows
  • php change profile image
  • combine multiple arrays into one array php
  • how to sum values in an array
  • how to get sum of the total time
  • How to Create Facebook live stream without notification using API ?
  • on button click make div visible
  • css affect another element on hover
  • insert line after paragraph word
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org