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

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*';

CodePudding user response：

Use LIKE:

UPDATE table_name SET cat2 = NULL WHERE cat2 LIKE 'A%';

CodePudding user response：

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';

CodePudding user response：

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;