完成sql server中的where子句

I have a table kyc3 where there are walletno, status and rank columns present. rank columns are currently filled with 0. While status column has following data: accepted, rejected, registered and scanned. I want to put value for each status in rank column where

我有一个表kyc3，其中有walletno、status和rank列。秩列当前填充为0。状态栏有以下数据:接受、拒绝、注册和扫描。我想把每个状态的值放在rank列

accepted = 1, rejected = 2, registered = 3 and scanned = 4

接受= 1，拒绝= 2，注册= 3，扫描= 4

I wrote following query but do not understand how to complete it:

我写了以下查询，但不明白如何完成:

INSERT INTO kyc3([rank]) SELECT status_ FROM kyc3

I understand I need to put a where clause that will indicate my logic for data population. But what should I write?

我知道我需要放一个where子句来指示我的数据填充逻辑。但是我应该写什么呢?

2 个解决方案

#1

You can use update to fill a cell of an existing row.

可以使用update填充现有行的单元格。

update kyc3
set rank = CASE WHEN status = 'accepted' THEN 1
                WHEN status = 'rejected' THEN 2
                WHEN status = 'registered' THEN 3
                WHEN status = 'scanned' THEN 4
           END

Use insert only for creating new rows.

只在创建新行时使用insert。

#2

If the table is populated and you want to change the rank field, you want to use an UPDATE statement, as INSERT is for adding new rows to your table:

如果填充了表，并且想要更改rank字段，则需要使用UPDATE语句，因为INSERT用于向表中添加新行:

UPDATE kyc3 
SET rank = CASE WHEN status = 'accepted' THEN 1
                  WHEN status = 'rejected' THEN 2
                  WHEN status = 'registered' THEN 3
                  WHEN status = 'scanned' THEN 4 
             END

#1