Solved

if statement in update mysql

Posted on 2015-01-06
6
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 66

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

624 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