Setting value for one column of all records in table
MysqlSqlMysql Problem Overview
I'm trying to clear one column for all records in my table.
For example, if my table had three columns: id
, comment
, and likes
- I would like to be able to clear the likes
column.
+---+-------+-----+
|id |comment|likes|
+-----------------+
|1 |hi |3 |
|2 |hello |12 |
|3 |hey |1 |
+---+-------+-----+
so that afterwards it would look like this:
+---+-------+-----+
|id |comment|likes|
+-----------------+
|1 |hi | |
|2 |hello | |
|3 |hey | |
+---+-------+-----+
I'm guessing I would have to use MySQL UPDATE
to clear the likes
value, but how do I iterate through all records and keep the id
and comment
fields the same?
I don't want to change each record manually.
Mysql Solutions
Solution 1 - Mysql
UPDATE your_table SET likes = NULL
or if your likes
column does not allow NULL
:
UPDATE your_table SET likes = ''
Some SQL tools that are used for executing DB queries prevent updates on ALL records (queries without a where
clause) by default. You can configure that and remove that savety setting or you can add a where
clause that is true
for all records and update all anyway like this:
UPDATE your_table
SET likes = NULL
WHERE 1 = 1
If you compare with NULL
then you also need the IS
operator. Example:
UPDATE your_table
SET likes = NULL
WHERE likes IS NOT NULL
because comparing NULL
with the equal operator (=
) returns UNKNOWN. But the IS
operator can handle NULL
.