update multiple values in one column using one sql query?

Question

I am working with huge MySQL database and need to set same two values for one table field.

My table is post3 and set two values 'attachment', 'image/jpeg' for post_type column.

I have tried following queries

UPDATE `post3`
SET post_type = ''
WHERE post_type IN ('attachment', 'image/jpeg');

The code is executed however, doesn't affect rows and getting following results

0 rows affected. (Query took 0.0038 seconds.)

Thanks.

Answer 1

For me this type of logic works fine, here is a fiddle showing it. Included an select before and after so that you can see that the change is done. In this case, I would assume that your table post3 simply does not have any post_type called 'attachment' or 'image/jpeg'. If so, please show us an select * from post3 with these shown. This works from MySQL v5.5 -> v8.0, at the very least. https://www.db-fiddle.com/f/xaEyFGU7xmhTk569MePY91/2

CREATE TABLE post3(
    id int,
    post_type  varchar(50)
);

INSERT INTO post3 (id, post_type) VALUES(1,'attachment');
INSERT INTO post3 (id, post_type) VALUES(2,'attachment');
INSERT INTO post3 (id, post_type) VALUES(3,'image/jpeg');
INSERT INTO post3 (id, post_type) VALUES(4,'image/jpeg');

select *
from `post3`;

UPDATE `post3`
SET post_type = ''
WHERE post_type IN ('attachment', 'image/jpeg');

select *
from `post3`;

First select:

id	post_type
1	attachment
2	attachment
3	image/jpeg
4	image/jpeg

Update query ran

Second select:

id	post_type
1
2
3
4