if statement in update mysql

I am trying to update with the max(DATETIME) and that is done but now I am trying to get it not to update the datetime to null if it has no value.
As you can see in the code I set the insert date to 2012-01-01 12:00:00 AM when I create the table but the update statement sets it to null if there is no datetime meeting those parameters - I want it to leave the date alone if there is nothing to update

DROP Table if exists TEMP_PARTS;

CREATE TABLE TEMP_PARTS(SELECT  il.PartDesc as 'Tool Type', il.PartNumber, ip.Quantity, CAST('2012-01-01 12:00:00 AM' AS DATETIME) AS 'InsertDate', ip.InventoryListID, ip.locationID
                FROM Inventory_Parts as ip
                JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID
                WHERE LocationID = 1 and isVisible = 1);
                
                
                UPDATE TEMP_PARTS tp
///////I was thinking maybe it would be an if statement here ?? /////////////////////////////////////
/////// if max(InsertDate ) IS NOT NULL ELSE (SKIP)

Set tp.InsertDate = (select max(InsertDate) from Inventory_PartsHistory WHERE InventoryListID = tp.InventoryListID AND  locationID=tp.LocationID);
                
                
                SELECT * FROM TEMP_PARTS Where InsertDate BETWEEN  
                '2012-01-01 12:00:00 AM' AND '2014-12-13 12:00:00 AM';

Open in new window

LVL 6
r3nderAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Daniel WilsonConnect With a Mentor Commented:
Let's try an update on a JOIN instead of an update from a subselect.
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

Update TEMP_PARTS tp Left Join
(Select InventoryListID, LocationID, Max(InsertDate) as MaxInsertDate from Inventory_PartsHistory 
Group By InventoryListID, LocationID) D on D.InventoryListID = tp.InventoryListID AND  D.locationID=tp.LocationID
Set tp.InsertDate = D.MaxInsertDate
Where D.MaxInsertDate is NOT NULL

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<get it not to update the datetime to null if it has no value.
Should be as simple as adding an 'WHERE datetime IS NULL' at the end of your SQL Statement

UPDATE ??? tp
Set tp.InsertDate = (
   select max(InsertDate) 
   from Inventory_PartsHistory 
   WHERE InventoryListID = tp.InventoryListID AND  locationID=tp.LocationID)
WHERE datetime_column IS NULL

Open in new window

0
 
r3nderAuthor Commented:
The problem is the insertdate column in the table I created is not null - I set it to '2012-01-01 12:00:00 AM when I created the table. Now I am running through and updating those insertdate fields that meet the criteria in the subselect. but my update is setting the insertDate field to null and updating the insertdate fields that match the criteria and I don't want it to change the date if it has no update
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
r3nderAuthor Commented:
Here is the temp_parts before the update and after  the update
TEMP-PARTSBefore.csv
TEMP-PARTSAfter.csv
0
 
r3nderAuthor Commented:
I tried this but I have something wrong with the syntax
DROP Table if exists TEMP_PARTS;
DROP Table if exists Temp_UpdateDates;

CREATE TABLE TEMP_PARTS(SELECT  il.PartDesc as 'Tool Type', il.PartNumber, ip.Quantity, CAST('2012-01-01 12:00:00 AM' AS DATETIME) AS 'InsertDate', ip.InventoryListID, ip.locationID
                FROM Inventory_Parts as ip
                JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID
                WHERE LocationID = 1 and isVisible = 1);
                
                
                UPDATE TEMP_PARTS tp
//////////////////////error near 'select max(InsertDate) from Inventory_PartsHistory WHERE InventoryListID = tp.In' at line 2 
Set tp.InsertDate = (CASE WHEN max(InsertDate) is not null THEN select max(InsertDate) from Inventory_PartsHistory WHERE InventoryListID = tp.InventoryListID AND  locationID=tp.LocationID ELSE InsertDate END);
                
                
                SELECT * FROM TEMP_PARTS Where InsertDate BETWEEN  
                '2012-01-01 12:00:00 AM' AND '2014-12-13 12:00:00 AM';

Open in new window

0
 
r3nderAuthor Commented:
Thank you Daniel - worked great
0
All Courses

From novice to tech pro — start learning today.