Link to home
Start Free TrialLog in
Avatar of r3nder
r3nderFlag for United States of America

asked on

Mysql insert or update depending on if record exists

I need to update a Table Temp_MyTools

using this criteria
Create Table TempMyUpdate(Select MAX(UploadDate) as UploadDate,SerialNumber, FROM Inventory_SerializedAssets Where LocationID in(1));
update Temp_MyTools mt
Join Temp_MyUpdate as mu on mu.serialnumber = mt.serialnumber
   set mt.FUploadTime = mu.UploadDate


if there is nothing to update then Insert

/////insert
INSERT INTO Temp_MyTools(Select ToolType,SerialNumber,MAX(UploadDate) as FUploadTime, Cast('2012:01:01 12:00:00 AM'as DateTime) AS 'HUploadTime',  LocationID as DefaultLocationIndex, 1 as Qty FROM Inventory_SerializedAssets Where LocationID in(1) GROUP BY SerialNumber);

the tricky part is that this is part of a large query so it is not just by itself how would I do this?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of r3nder

ASKER

would this work?
Create Table Temp_Update(Select MAX(UploadDate) as UploadDate,SerialNumber, FROM Inventory_SerializedAssets Where LocationID in(" + locationIndex + ")); 

             IF(Select * From Temp_MyTools Where SerialNumber in(Select SerialNumber from Temp_Update )IS NULL THEN  
                         INSERT INTO Temp_MyTools(Select ToolType,SerialNumber,MAX(UploadDate) as FUploadTime, Cast('2012:01:01 12:00:00 AM'as DateTime) AS 'HUploadTime',  LocationID as DefaultLocationIndex, 1 as Qty FROM Inventory_SerializedAssets Where LocationID in('" + locationIndex + "') GROUP BY SerialNumber);  
             ELSE UPDATE Temp_MyTools mt 
             Join Temp_MyUpdate as mu on mu.serialnumber = mt.serialnumber 
             set mt.FUploadTime = mu.UploadDate 

Open in new window

it will likely not work, as you will have some value on serialnumber that already are in the table, and others are not.
either then you run this per single serial number, or use the syntax I proposed
Avatar of r3nder

ASKER

like this
INSERT INTO Temp_MyTools(Select ToolType,SerialNumber,MAX(UploadDate) as FUploadTime, Cast('2012:01:01 12:00:00 AM'as DateTime) AS 'HUploadTime',  LocationID as DefaultLocationIndex, 1 as Qty FROM Inventory_SerializedAssets Where LocationID in('" + locationIndex + "') GROUP BY SerialNumber)  ON DUPLICATE KEY UPDATE FUploadTime = VALUES(FUploadTime);

Open in new window

that looks good :)
Avatar of r3nder

ASKER

Worked like a champ ! Thanks Guy