CODEWITHSUNDEEP
mysql
Matthew Colley
Matthew Colley

Reputation: 11436

MYSQL Insert values into another table if values not exists

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`,

Upvotes: 0

Views: 138

Answers (2)

xQbert
xQbert

Reputation: 35323

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.

Upvotes: 1

jobs
jobs

Reputation: 63

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.

Upvotes: 0

Related Questions