Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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?
0
r3nder
Asked:
r3nder
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to use the insert with on duplicate key update syntax:
http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
0
 
r3nderAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
r3nderAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that looks good :)
0
 
r3nderAuthor Commented:
Worked like a champ ! Thanks Guy
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now