Use 'IF' to detect NULL in mysql update query

Question

I want to give the user the choice to 'overwrite' values in a field in a table or 'add' to the value in the table. The problem is that sometimes the field in the table is null so if the user chooses to 'add' then nothing is updated because mysql is adding null to 10 for instance

I then tried to use the 'IF' statement to detect if the field in the table is null so I can do straight update and if not then I add the new value to the value already in the table.

Query:

UPDATE
   table1 
   INNER JOIN
      table2 
      ON table1.ID = table2.Table1ID 
SET
   table1.column = 
   (
      IF table1.column = NULL 
   THEN
      table2.column 
   ELSE
      table1.column + table2.column
   )

I have an error that says there is a problem with my sql statement. I have tried using 'IS NULL' instead of '= NULL' but it doesn't work either

forpas · Accepted Answer · 2019-10-09 20:30:21Z

1

IF() is a function, but you use it like the CASE statement. So you get syntax error.
Use it like this:

UPDATE table1 INNER JOIN table2 
ON table1.ID = table1.Table1ID 
SET table1.column= IF(table1.column IS NULL, table2.column, table1.column + table2.column)

or use a CASE statement:

UPDATE table1 INNER JOIN table2 
ON table1.ID = table1.Table1ID 
SET table1.column = CASE 
  WHEN table1.column IS NULL THEN table2.column
  ELSE table1.column + table2.column
END

or use COALESCE():

UPDATE table1 INNER JOIN table2 
ON table1.ID = table1.Table1ID 
SET table1.column = COALESCE(table1.column, 0) + table2.column

answered Oct 9, 2019 at 20:30

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jarlh Over a year ago

case expression.

Percy Kumah Over a year ago

Worked. Thank you!

forpas Over a year ago

@jarlh yes and no. Here: dev.mysql.com/doc/refman/5.7/en/case.html it is refrerred as statement. Here: learn.microsoft.com/en-us/sql/t-sql/language-elements/… as an expression. In most sql oriented web sites it is referred as statement. I believe the correct technical term is expression but ................

jarlh Over a year ago

The MySQL documentation is a bit confusing. A case expression returns a value and can be used in a SELECT etc. A case statement is for conditional execution of code, e.g in a stored procedure or trigger.

forpas Over a year ago

It's not just the MySql documentation. Just google "CASE sql". Most results will have: "The CASE statement...."

Bilal Siddiqui · Accepted Answer · 2019-10-09 20:35:00Z

1

You need to use IF ( table1.column is null then ...........)

instead = operator you need IS NULL or IS NOT NULL in case of null checking

Additionally, your ON clause seems have a typo .. it should be table1.ID = table2.Table1ID

Update

I missed the syntax error completely, so change your set clause to:

SET table1.column = (IF table1.column IS NULL, table2.column, table1.column + table2.column )

For your reference: https://www.electrictoolbox.com/mysql-using-if-in-where/

edited Oct 9, 2019 at 20:35

answered Oct 9, 2019 at 20:08

Bilal Siddiqui

3,6491 gold badge16 silver badges22 bronze badges

3 Comments

Percy Kumah Over a year ago

Thanks Bilal. I changed the = operator to IS NULL but still have the same error. Regarding the typo I typed it wrong here but in the actual code it is correct. The error is "You have an error in your mysql syntax....near 'table1.column IS NULL THEN'....

Bilal Siddiqui Over a year ago

ohhh yeah! there is syntax error. I am updating my answer

Bilal Siddiqui Over a year ago

Done !! Sorry as I am on cell phone, so missed things in smaller font and also your query was one liner initially. It should work now !! enjoy :)

Booboo · Accepted Answer · 2019-10-09 20:31:35Z

0

First, nothing compares equal to NULL, not even NULL. Second, yow should use the IF function:

UPDATE table1 INNER JOIN table2 ON table1.ID = table2.Table1ID
   SET table1.column = IF(table1.column IS NULL, table2.column, table1.column + table2.column)

or

UPDATE table1 INNER JOIN table2 ON table1.ID = table2.Table1ID
   SET table1.column = IFNULL(table1.column, 0) + table2.column

answered Oct 9, 2019 at 20:31

Booboo

45.7k4 gold badges46 silver badges74 bronze badges

Collectives™ on Stack Overflow

Use 'IF' to detect NULL in mysql update query

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related