英文:
Updating two rows in the same column with one string query sql
问题
我试图在同一张表中将值为1的活动行在激活另一行时更改为0（并将新行从0更改为1）。
UPDATE SpecialTBL
SET Active = 0
WHERE Active = 1 AND SpecID = @SpecID;
UPDATE SpecialTBL
SET Active = 1
WHERE Active = 0 AND SpecID = @SpecID;
这两个语句单独运行正常，但无法嵌套它们在一起。
英文:
I'm trying to make an active row with value 1 change to 0 when activating another row (and turning the new row from 0 to 1) in the same table.
UPDATE SpecialTBL
SET Active = 0
WHERE Active = 1 AND SpecID = @SpecID;
UPDATE SpecialTBL
SET Active = 1
WHERE Active = 0 AND SpecID = @SpecID;
Both these works separately but I can't nestle them together.
答案1
得分: 3
以下是翻译好的部分：
你只需使用算术操作：
UPDATE SpecialTBL
SET Active = 1 - Active
WHERE SpecID = @SpecID
如果可能存在除0和1以外的其他活动值，则我们需要另一个过滤条件：
UPDATE SpecialTBL
SET Active = 1 - Active
WHERE SpecID = @SpecID AND Active in (0,1)
否则，使用 CASE 表达式更常规：
UPDATE SpecialTBL
SET Active = CASE Active WHEN 1 THEN 0 ELSE 1 END
WHERE SpecID = @SpecID AND Active in (0,1)
英文:
You can just use arithmetic:
UPDATE SpecialTBL
SET Active = 1 - Active
WHERE SpecID = @SpecID
If there may be other active values than 0 and 1, then we would need another filtering condition:
UPDATE SpecialTBL
SET Active = 1 - Active
WHERE SpecID = @SpecID AND Active in (0,1)
Else, a case expression is more conventional:
UPDATE SpecialTBL
SET Active = CASE Active WHEN 1 THEN 0 ELSE 1 END
WHERE SpecID = @SpecID AND Active in (0,1)
答案2
得分: 1
你可以使用 ~ 操作符来对 BIT 数据类型进行按位取反。
UPDATE SpecialTBL SET Active = ~Active WHERE SpecID = @SpecID
英文:
You could use the ~ bitwise NOT for BIT data type.
UPDATE SpecialTBL SET Active = ~Active
WHERE SpecID = @SpecID
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论