• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Update Date Column only if blank

I want to add to an SQL statement something like DateColumn=' "&pDate& "'   , but I want it to update only if the field is blank. How do I do that?
0
net_susan
Asked:
net_susan
  • 4
  • 4
  • 3
4 Solutions
 
adwisemanCommented:
Update it = to itself if it is not null

Update Table
Set DateColumn = CASE WHEN DateColumn IS NULL THEN '"&pDate& " ELSE DateColumn END
0
 
rafranciscoCommented:
You can either use a where clause like this:

UPDATE YourTable
SET DateColumn = YourDate
WHERE DateColumn IS NULL OR DateColumn = ''

or you can do it this way

UPDATE YourTable
SET DateColumn = CASE WHEN DateColumn = '' OR DateColumn IS NULL THEN YourDate ELSE DateColumn END

The first approach is better.
0
 
adwisemanCommented:
rafrancisco is right about his first method, if you only want to update the records where the datecolumn is null.  If other records also need updated, the case methode would have to be used.

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
net_susanAuthor Commented:
Thanks, butI can't make it work (and it does before adding the SET, CASE, END part. What am I doing wrong?

 mySQL="UPDATE DateColumn SET DateColumn= CASE WHEN DateColumn = ' ' OR DateColumn IS NULL THEN ' "&pDate& "' ELSE DateColumn END, SeenColumn=SeenColumn+1, SecondDate=' "&pDate& "' WHERE somevar="&psomevar& "AND othervar="&pothervar
0
 
adwisemanCommented:
I don't think you update is correct

Correct syntex is

Update Table name
set Field = Value
0
 
net_susanAuthor Commented:
Sorry, I did have it like that (it still doesn't work):

mySQL="UPDATE someTable SET DateColumn= CASE WHEN DateColumn = ' ' OR DateColumn IS NULL THEN ' "&pDate& "' ELSE DateColumn END, SeenColumn=SeenColumn+1, SecondDate=' "&pDate& "' WHERE somevar="&psomevar& "AND othervar="&pothervar
0
 
rafranciscoCommented:
When you say "it still doesn't work", was there a syntax error or were the records not updated as expected?
0
 
rafranciscoCommented:
The last part should have been:

"...were the records not updated when they should have been?"
0
 
net_susanAuthor Commented:
yes, they were not updated
0
 
rafranciscoCommented:
Maybe the DateColumn is not null or empty string anymore.  Try issuing this SELECT statement and see if you get any records:

SELECT * FROM SomeTable
WHERE somevar = 'somevar' and othervar = 'othervar' and (DateColumn IS NULL OR DateColumn = '')
0
 
net_susanAuthor Commented:
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now