Update Field When Not Null

Question

I have an update statement that updates fields x, y and z where id = xx.

In the table I have a few different x_created_datetime fields (for different portions of the record that are maintained/entered by different folks). I'd like to write a single query that will update this field if is null, but leave it alone if is not null.

So what I have is:

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp"
WHERE id = X

What I need is a way to add in the following, but still always update the above:

scan_created_date = "current_unix_timestamp"
where scan_created_date is null

I'm hoping I can do this without a second transaction to the DB. Any ideas on how to accomplish this?

Answer 1

Do this:

UPDATE newspapers
SET scan_notes = "data",    
  scan_entered_by = "some_name",    
  scan_modified_date = "current_unix_timestamp",
  scan_created_date = COALESCE(scan_Created_date, "current_unix_timestamp")
WHERE id = X

The COALESCE function picks the first non-null value. In this case it will update the datestamp to be the same value if it exists, else it will take whatever you replace "current_unix_timestamp" with.

Answer 2

You could use COALESCE() wich returns the first NON-NULL value):

scan_created_date = COALESCE(scan_created_date, "current_unix_timestamp")

Answer 3

I think that what you're looking for is IF()

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp",
    scan_created_date = IF(scan_created_date IS NOT NULL, "current_unix_timestamp", NULL)
WHERE id = X

Answer 4

mySQL has an IFNULL function, so you could do:

UPDATE newspapers
SET scan_notes = "data",
    scan_entered_by = "some_name",
    scan_modified_date = "current_unix_timestamp"
    scan_created_date = IFNULL( scan_created_date, "current_unix_timestamp" )
WHERE id = X

Answer 5

You can do something like this:

UPDATE newspapers a, newspapers b
SET a.scan_notes = "data",    
  a.scan_entered_by = "some_name",    
  a.scan_modified_date = "current_unix_timestamp",
  b.scan_created_date = "current_unix_timestamp"
WHERE a.id = X AND b.id = X AND b.scan_created_date is not NULL