时间戳

首页 > 解决方案 > 将值从 case 表达式插入到表中

sql - 将值从 case 表达式插入到表中

问题描述

此案例表达式确定当前的批准者值。每次执行此案例表达式时，我想将当前的批准者值插入列（都是同一张表）中。CURRENT_APPROVER您是否通过使用 Insert into 来完成此操作？我在语法上有问题。

SELECT 
    CASE 
       WHEN FORM_TYPE IN ('Form_Type1', 'Form_Type2', 'Form_Type3') 
            AND SIGNATURE_FIELD1 IS NULL 
            AND STATUS = 'Pending Approval' 
          THEN 'Signature 1 Needed'
       WHEN FORM_TYPE IN ('Form_Type1', 'Form_Type2', 'Form_Type3') 
            AND SIGNATURE_FIELD2 IS NULL 
            AND STATUS = 'Pending Approval' 
           THEN SIGNATURE_FIELD1_ASSIGNMENT
        WHEN FORM_TYPE IN ('Form_Type1', 'Form_Type2', 'Form_Type3') 
            AND SIGNATURE_FIELD3 IS NULL 
            AND STATUS = 'Pending Approval'  
           THEN SIGNATURE_FIELD2_ASSIGNMENT
        WHEN FORM_TYPE IN ('Form_Type1', 'Form_Type2', 'Form_Type3') 
             AND SIGNATURE_FIELD4 IS NULL 
             AND SIGNATURE_FIELD2 IS NOT NULL 
             AND SIGNATURE_FIELD3 IS NOT NULL 
             AND SIGNATURE_FIELD1 IS NOT NULL 
             AND STATUS = 'Pending Approval'  
            THEN 'Signature 4 Needed'
        ELSE 'Unavailable'
    END AS CURRENT_APPROVER
FROM 
    FINANCE_TABLE;

标签： sql

解决方案

这会将CURRENT_APPROVERSELECT CASE 中的值为 null 的值（如果该列的所有行都为 null，则可以删除最后一个 WHERE 条件）。要使用正确的值更新正确的行，您需要通过表的主键将内部SELECT查询连接到查询。UPDATE我用作id示例，但您需要将其替换为真正的主键

UPDATE FINANCE_TABLE f
SET CURRENT_APPROVER = (SELECT 
                        CASE 
                        WHEN FORM_TYPE IN ('Form_Type1','Form_Type2','Form_Type3') AND SIGNATURE_FIELD1 IS NULL AND STATUS = 'Pending Approval' THEN 'Signature 1 Needed'
                        WHEN FORM_TYPE IN ('Form_Type1','Form_Type2','Form_Type3') AND SIGNATURE_FIELD2 IS NULL AND STATUS = 'Pending Approval' THEN SIGNATURE_FIELD1_ASSIGNMENT
                        WHEN FORM_TYPE IN ('Form_Type1','Form_Type2','Form_Type3') AND SIGNATURE_FIELD3 IS NULL AND STATUS = 'Pending Approval'  THEN SIGNATURE_FIELD2_ASSIGNMENT
                        WHEN FORM_TYPE IN ('Form_Type1','Form_Type2','Form_Type3') AND SIGNATURE_FIELD4 IS NULL AND SIGNATURE_FIELD2 IS NOT NULL AND SIGNATURE_FIELD3 IS NOT NULL AND SIGNATURE_FIELD1 IS NOT NULL AND STATUS = 'Pending Approval'  THEN 'Signature 4 Needed'
                        ELSE 'Unavailable'
                        END AS CURRENT_APPROVER
                        FROM FINANCE_TABLE f2
                        WHERE f.id = f2.id) -- This needs to be updated

WHERE f.CURRENT_APPROVER IS NULL;

我在这里使用这个答案作为我的答案的模板

推荐阅读