-- ----------------------------
-- 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;
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