Reversing column values in mysql command line

Question

I have a table posts with the column published, which is either 0 (unpublished) or 1 (published). Say I want to make all the published posts into unpublished posts and all the unpublished posts into published posts. I know that running

UPDATE posts SET published = '1' WHERE published = '0';
UPDATE posts SET published = '0' WHERE published = '1';

will end up turning all my posts into published posts. How can I run these queries in the mysql command line so that it truly "reverse" the values, as opposed to the mistake outlined above?

Thanks

EDIT: assume the data types are strings. I know ints/bools are a much better way to do this, but I'm working with strings, and changing the schema is not an option.

Answer 1

If this seems really difficult, you haven't been thinking about it enough.

UPDATE posts SET published = 1 - published;

Should do it, or some other solutions (such as using XOR, or CASE).

Answer 2

For strings - you can use any expression in the SET clause.

UPDATE posts SET published = IF(published = "1", "0", "1");

CASE version, thanks OMG Ponies:

UPDATE posts SET published = CASE published WHEN "1" THEN "0" ELSE "1" END;

In both versions, if published IS NULL, it will set to "1" as any comparison with NULL is false.

Answer 3

Use:

UPDATE posts
   SET published = CASE 
                     WHEN published IS NULL THEN NULL
                     WHEN published = '1' THEN '0'
                     ELSE '1' 
                   END

Answer 4

What do you guys think of this:

UPDATE posts SET published = 'A1' WHERE published = 'A';
UPDATE posts SET published = 'B' WHERE published = 'A';
UPDATE posts SET published = 'A' WHERE published = 'A1';

EDIT: this isn't remotely correct