0

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.

Binod Bhattarai
  • 3
  • 3
  • What about if you try `WHERE post_type = "attachment" OR post_type = "image/jpeg" does it work? As well when you do a SELECT with the conditional you are using, Does it returns something? What does it return? – David Valladares L. Apr 07 '22 at 13:10
  • I'm tried with `WHERE post_type = "attachment" OR post_type = "image/jpeg" and it doesn't work. – Binod Bhattarai Apr 07 '22 at 13:13
  • Seems to be working for me. If it is not, I would suspect that you simply don't have data that matches this criteria. Can you show us? See my response with fiddle to see what I mean. – Stoff Apr 07 '22 at 13:17
  • @Binod Bhattarai that's weird, as well doing a `SELECT COUNT(*)` from the items that have post_type like your conditional returns something greater than 0? Does your column contains exactly "attachment" or "image/jpeg" ? Or that is part of a string? – David Valladares L. Apr 07 '22 at 13:19

1 Answers1

0

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
Stoff
  • 517
  • 6
  • 22