CODEWITHSUNDEEP
mysqlsqlsql-update
Monty
Monty

Reputation: 1311

MySQL UPDATE query not updating all columns?

update short_url 
  set redirected="2018-08-10 15:07:44", 
  count=count+1 
  where long_val="EF7219D6-606D-409B-BEF1-00FFF9FF713C";

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| short_val  | varchar(8)  | NO   | PRI | NULL    |       |
| long_val   | varchar(36) | NO   | PRI | NULL    |       |
| created    | datetime    | YES  |     | NULL    |       |
| redirected | datetime    | YES  |     | NULL    |       |
| count      | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

The redirected date is updated with this update, but count does not add ?

Upvotes: 1

Views: 974

Answers (2)

spencer7593
spencer7593

Reputation: 108450

We can test for a NULL value, and replace a NULL with a non-null value, for example ...

UPDATE short_url t 
   SET t.redirected = '2018-08-10 15:07:44'
     , t.count = IFNULL( t.count ,0) + 1
--               ^^^^^^^         ^^^
 WHERE t.long_val = '...'

If the current value of count is NULL, then we will assign a value of 1 to it.

Note that most expressions that involve a NULL value will evaluate to NULL. We can handle NULL values using the IS NULL conditional test, the IFNULL() function, the ANSI standards compliant COALESCE function, et al.

Upvotes: 1

Martijn Willegers
Martijn Willegers

Reputation: 11

What you need to do is make sure the default value of the field 'count' is set to 0 and not NULL

Upvotes: 0

Related Questions