Mysql update is not working


#1

mysql> SELECT DATABASE();
±--------------+
| DATABASE() |
±--------------+
| retail_export |
±--------------+
1 row in set (0.00 sec)

mysql> select * from departments_hive01;
±-----±----------------±-----------+
| id | department_name | avg_salary |
±-----±----------------±-----------+
| 2 | Fitness | NULL |
| 3 | Footwear | NULL |
| 4 | Apparel | NULL |
| 5 | Golf | NULL |
| 6 | Outdoors | NULL |
| 7 | Fan Shop | NULL |
| 777 | Not known | 1000 |
| 8888 | NULL | 1000 |
| 666 | NULL | 1100 |
±-----±----------------±-----------+
9 rows in set (0.00 sec)

mysql> udpate departments_hive01 set department_name = ‘’ where department_name is null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘udpate departments_hive01 set department_name = ‘’ where department_name is null’ at line 1
mysql>

This is really weird!


#2

However, I drilled down more here:

I created a new table dep with the same structure;
I copid all records from the hive01 table;
I then did the same update command and it worked!

mysql> create table dep (id int, department_name varchar(20), avg_salary int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc dep;
±----------------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| department_name | varchar(20) | YES | | NULL | |
| avg_salary | int(11) | YES | | NULL | |
±----------------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

mysql> insert into dep select * from departments_hive01;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> select * from dep;
±-----±----------------±-----------+
| id | department_name | avg_salary |
±-----±----------------±-----------+
| 2 | Fitness | NULL |
| 3 | Footwear | NULL |
| 4 | Apparel | NULL |
| 5 | Golf | NULL |
| 6 | Outdoors | NULL |
| 7 | Fan Shop | NULL |
| 777 | Not known | 1000 |
| 8888 | NULL | 1000 |
| 666 | NULL | 1100 |
±-----±----------------±-----------+
9 rows in set (0.00 sec)

mysql> update dep set department_name = ‘’ where department_name is null;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * From dep;
±-----±----------------±-----------+
| id | department_name | avg_salary |
±-----±----------------±-----------+
| 2 | Fitness | NULL |
| 3 | Footwear | NULL |
| 4 | Apparel | NULL |
| 5 | Golf | NULL |
| 6 | Outdoors | NULL |
| 7 | Fan Shop | NULL |
| 777 | Not known | 1000 |
| 8888 | | 1000 |
| 666 | | 1100 |
±-----±----------------±-----------+
9 rows in set (0.00 sec)

This is really driving nut. Any idea? Thank you very much.


#3

@paslechoix Spelling mistake here update

You can replace Null with 0 in avg_salary

image


#4

Thank you for catching that, in fact, it just turned out that for some unknown reason, when I copy paste my command prepared in notepad++, the single quote is changed, when I typed in the command in cgywin one character by one character, it works


#5