Reversing column values in mysql command line
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开发者_StackOverflow 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.
Use:
UPDATE posts
SET published = CASE
WHEN published IS NULL THEN NULL
WHEN published = '1' THEN '0'
ELSE '1'
END
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).
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.
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
精彩评论