WITH a1(a,b) AS ( SELECT 1,'hello' UNION all SELECT 2,'hi' ) ,a2 AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE()) re FROM a1 ) SELECT (SELECT a FROM a2 WHERE re=CASE WHEN a.re=1 THEN 2 ELSE 1 end) a,b FROM a2 a
update tb set a=2 where b=hello; update tb set a=1 where b=hi;
update tb set a=if(a=1,2,1);
update tb set a=2 where b=hello; updateupdate tb set a=2 where b=hello; update tb set a=1 where b=hi; tb set a=1 where b=hi;
update table1 set a=if(a=2,1,2) where a in (1,2)
-- ---------------------------- -- Table structure for `test` -- ---------------------------- DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `a` int(11) NOT NULL, `b` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of test -- ---------------------------- INSERT INTO `test` VALUES ('2', 'hello'); INSERT INTO `test` VALUES ('1', 'hi'); select * from test;update test set a=2 where b='hello'; update test set a=1 where b='hi'; select * from test;
a b
2 hello
1 hi
(
SELECT 1,'hello' UNION all
SELECT 2,'hi'
)
,a2 AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE()) re FROM a1
)
SELECT (SELECT a FROM a2 WHERE re=CASE WHEN a.re=1 THEN 2 ELSE 1 end) a,b
FROM a2 a
update tb set a=1 where b=hi;
updateupdate tb set a=2 where b=hello;
update tb set a=1 where b=hi; tb set a=1 where b=hi;
set a=if(a=2,1,2)
where a in (1,2)
-- Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`a` int(11) NOT NULL,
`b` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('2', 'hello');
INSERT INTO `test` VALUES ('1', 'hi');
select * from test;update test set a=2 where b='hello';
update test set a=1 where b='hi';
select * from test;