CODEWITHSUNDEEP
mysqlsql
Linas
Linas

Reputation: 4408

Mysql update if value doesn't exist

I'm trying to write quite strange query and i'm having problem with if statement, so i have 2 fields deleted_1 and deleted_2 in my table which i wanna update, so this how i think it should be:

Update `messages`
  if(`deleted_1` == null) SET `deleted_1` = 1
  else
  if(`deleted_2` == null) SET `deleted_2 = 1
  ---and here goes some simple WHERE statements

but this doesn't work so basicly i wanna check if a field is empty then update it if not check b field and if it's empty update b field now i'm not sure maybe it's possible to do it in more simple way, i'm quite lost here :)

Upvotes: 1

Views: 1860

Answers (2)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

UPDATE MyTable
SET deleted_2 = CASE WHEN ISNULL(deleted_1, '') == '' OR ISNULL(deleted_2, '') != '' THEN deleted_2 ELSE 1 END,
    deleted_1 = CASE WHEN ISNULL(deleted_1, '') != '' THEN deleted_1 ELSE 1 END

this is saying:

deleted_1: will be update if (deleted_1 == null)
deleted_2: will be updated if (deleted_1 != null && deleted_2 == null), 
           This is to match your if else condition

Upvotes: 3

TrippyD
TrippyD

Reputation: 735

I think your problem is that null != null. the expression deleted_2 == null will never resolve as true. First off, single =, not double (at least in mysql), secondly, try these:

mysql> select false = null;
+--------------+
| false = null |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

mysql> select true = null;
+-------------+
| true = null |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select null != null;
+--------------+
| null != null |
+--------------+
|         NULL |
+--------------+
1 row in set (0.01 sec)

mysql> select null = null;
+-------------+
| null = null |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

Your expression needs to either be "where table.column is null", "where table.column is not null", or some variation of ifnull(): https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

Upvotes: 1

Related Questions