Question 22194 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql – how to clear some fields content that contains the specific value

I have a database in mysql. I want clear field contents that contains specific value.

I have database like this:

`id`    `cat1` `cat2` `cat3`
 1       1185    A1    3185
 2       1585    A2    3131
 3       2513    B3    3113
 4       3369    C4    3333

I want to clear only ‘fields’ (not entire row) that contain "A" in cat2 column.
how should I do that?

my query doesn’t seem to work properly.
instead delete entire row.

DELETE FROM table_name WHERE cat2='A*';
Tags:

3

Answers


  1. 0

    Use LIKE:

    UPDATE table_name SET cat2 = NULL WHERE cat2 LIKE 'A%';
    Login or Signup to reply.
  2. 0

    You can use UPDATE to update the value to NULL or an empty string instead of deleting the entire row/column. Use REGEXP to implement regex.

    UPDATE EMPLOYEE SET cat2='' WHERE cat2 REGEXP '^A';
    Login or Signup to reply.
  3. 0

    How about overwrite cat2 with a CASE expression? Verbose but you can clearly see what’s going on.

    SELECT id, cat1, 
  CASE
    WHEN cat2 NOT LIKE 'A%' THEN cat2
  END AS cat2
  ,cat3
FROM my_table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search