CODEWITHSUNDEEP
mysqlsql
user1199434
user1199434

Reputation: 723

Setting value for one column of all records in table

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.

Upvotes: 57

Views: 90844

Answers (1)

juergen d
juergen d

Reputation: 204854

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.

Upvotes: 102

Related Questions