Solved

if statement in update mysql

Posted on 2015-01-06
6
136 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Importing and exporting data Magento 1.x ? 4 34
mySql Syntax 7 27
ms sql stored procedure 22 77
while loop in html mail format 5 32
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

18 Experts available now in Live!

Get 1:1 Help Now