Solved

if statement in update mysql

Posted on 2015-01-06
6
146 Views
Last Modified: 2015-01-06
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

0
Comment
Question by:r3nder
  • 4
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40533553
<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
 
LVL 6

Author Comment

by:r3nder
ID: 40533562
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
 
LVL 6

Author Comment

by:r3nder
ID: 40533575
Here is the temp_parts before the update and after  the update
TEMP-PARTSBefore.csv
TEMP-PARTSAfter.csv
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 6

Author Comment

by:r3nder
ID: 40533615
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 40533707
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
 
LVL 6

Author Closing Comment

by:r3nder
ID: 40533757
Thank you Daniel - worked great
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now