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?

score 43 · Accepted Answer · edited Aug 29 '17 at 09:54

43

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 scan_created_date to be the same value if it exists, else it will take whatever you replace "current_unix_timestamp" with.

edited Aug 29 '17 at 09:54

anshulk

458
5
13

answered Dec 07 '09 at 14:16

cjk

45,739
9
81
112

3

It's worth a brief explanation of why this works: coalesce returns the first non-null value in your list of values, so if the scan_created_date is already there, it will set itself to the value that's already there. If it's null, it will set itself to the current timestamp. – Jacob Mattison Dec 07 '09 at 14:19
Won't this update the scan_created_date when it is null? It looks like he wants to update it where it is not null. – tloach Dec 07 '09 at 14:19
@tloach - see the explanation. @JacobM - was already doing it :) – cjk Dec 07 '09 at 14:21
1

@ck: This still looks like it's doing the reverse of what he asked. This will update if scan_created_date is NULL, the query he gives explicitly only updates if scan_created_date is NOT NULL. – tloach Dec 07 '09 at 14:23
in other words, in his example, a null value will remain null - it will not be set. – tloach Dec 07 '09 at 14:24
@tloach, you are correct. I typod the part in the code example, but was correct in the english description. I've updated that now. What ck provided is what was desired and works perfectly. – Cory Dee Dec 07 '09 at 14:33
UPDATE newspapers SET scan_notes = "data", scan_entered_by = "some_name", scan_modified_date = "current_unix_timestamp", scan_created_date = COALESCE(updated_scan_created_date, "current_unix_timestamp") WHERE id = X updated_scan_created_date => to specify this is coming from the inputed value not from the table column value. – XanderDwyl Feb 01 '23 at 01:42

score 7 · Answer 2 · answered Dec 07 '09 at 14:19

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

score 6 · Answer 3 · answered Dec 07 '09 at 14:18

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

score 3 · Answer 4 · answered Dec 07 '09 at 14:17

3

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

scan_created_date = COALESCE(scan_created_date, "current_unix_timestamp")

answered Dec 07 '09 at 14:17

AdaTheDev

142,592
28
206
200

score 2 · Answer 5 · answered Dec 07 '09 at 14:21

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

score 1 · Answer 6 · answered Sep 29 '13 at 20:07

Its like equivalent to Oracle's NVL. You can use it like below in a prepared statement using parameters

UPDATE
    tbl_cccustomerinfo
SET
    customerAddress = COALESCE(?,customerAddress),
    customerName =  COALESCE(?,customerName),
    description =  COALESCE(?,description)
WHERE
    contactNumber=?

Update Field When Not Null

6 Answers6

Linked

Related