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
Solved

if statement in update mysql

Posted on 2015-01-06
6
155 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to just get time from a date 6 32
SQL Select in Access 2003 3 23
Requesting help with creating an SQL query with 2 tables 6 24
SQL - Simple Pivot query 8 13
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Creating and Managing Databases with phpMyAdmin in cPanel.
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.
Viewers will learn how the fundamental information of how to create a table.

839 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