MYSQL Insert values into another table if values not exists

Question

I am writing from one Mysql table to another, and I need to check if the values I am inserting already exist. I have tried setting indexes on the two columns I am checking values (onhand.Sales_Order & onhand.SKU)

Here is my query:

INSERT INTO onhand(Sales_Order, SKU, Inventory, Time)
  SELECT Sales_Order, Part_Number, '0', Now() AS DateTimeStamp from 
  fullfillment_sage WHERE New_OnHand IN (-1, 0, 'NULL') OR New_OnHand IS NULL
WHERE NOT EXISTS (SELECT 
  `fullfillment_sage`.`Sales_Order`, 
  `fullfillment_sage`.`Part_Number`
   FROM `fullfillment_sage`
   LEFT JOIN `onhand`
   ON `fullfillment_sage`.`Sales_Order` = `onhand`.`Sales_Order`
   AND `fullfillment_sage`.`Part_Number` = `onhand`.`SKU` 
   WHERE `fullfillment_sage`.`New_OnHand` IN (-1, 0, 'NULL') OR 
   `fullfillment_sage`.`New_OnHand` IS NULL);

The query errors here:

 'WHERE NOT EXISTS (SELECT `fullfillment_sage`.`Sales_Order`,

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT — Matthew Colley, Apr 21 '17 at 14:08
Possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — gabe3886, Apr 21 '17 at 14:09
agreed, googled this first, replace into and ignore into does not seem to be a plausible sceanrio due to current structure — Matthew Colley, Apr 21 '17 at 14:11
Where not supported on an insert?!?! https://dev.mysql.com/doc/refman/5.7/en/insert-select.html then why do doc's show it in an example!? The problem is you have 2 where statements.. put the first in ()'s and change the 2nd to an and. — xQbert, Apr 21 '17 at 14:12
agreed with @xQbert - where clause is supported on an insert. — Matthew Colley, Apr 21 '17 at 14:14
and @MatthewColley ok... so what is 'NULL' for new_onhand is it really a 'NULL' string or is it a null value we need to handle? and one last question: you are CREATING a new table right (onhand?) if not then `SELECT ... INTO TABLE creates a NEW table with the rows from the SELECT, this is completely different from INSERT ... SELECT, which just appends rows to an existing table.` per usercomment: https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-select-into-table.html — xQbert, Apr 21 '17 at 14:24

score 1 · Accepted Answer · answered Apr 21 '17 at 14:15

You have two where statements in your outer select. Wrap your or in ()'s for the first one and change the 2nd where to an and.

INSERT INTO onhand(Sales_Order, SKU, Inventory, Time)
  SELECT Sales_Order
       , Part_Number
       , 0
       , Now() AS DateTimeStamp 
  FROM fullfillment_sage 
  WHERE (New_OnHand IN (-1, 0, 'NULL') OR New_OnHand IS NULL)
    AND NOT EXISTS (SELECT `fullfillment_sage`.`Sales_Order`
                         , `fullfillment_sage`.`Part_Number`
                    FROM `fullfillment_sage`
                    LEFT JOIN `onhand`
                      ON `fullfillment_sage`.`Sales_Order` = `onhand`.`Sales_Order`
                     AND `fullfillment_sage`.`Part_Number` = `onhand`.`SKU` 
                    WHERE `fullfillment_sage`.`New_OnHand` IN (-1, 0, 'NULL') 
                      OR  `fullfillment_sage`.`New_OnHand` IS NULL);

This assumes there are no other issues with the select, I've not looked closely enough yet.

I don't think inventory of string '0' should be inserted I think it should be 0. though implicit casting is probably helping you there.

maybe new_onhand needs to elimninate 'null' since you have the or.... or you could change (New_OnHand IN (-1, 0, 'NULL') OR New_OnHand IS NULL) to... coalesce(New_OnHand,0) IN (-1, 0) if New_Onhand is null set it to zero and make sure new_onhand is either -1 or 0.

score 0 · Answer 2 · answered Apr 21 '17 at 14:10

0

As @juergend d mentioned in the comments, insert does not support where. I suggest you to create an if-statement that checks if this dataset exists. If not, execute the inesert query.

answered Apr 21 '17 at 14:10

jobs

63
1
9

MYSQL Insert values into another table if values not exists

2 Answers2