我被要求将我们的某些SQL字段更新为更现代的单数术语。因此,现在我正在将2列合并为一个新值,并将该新值输入到另一个表中。我知道我希望老年人现在等于什么,我只是不确定如何用更少的文字和正确的术语来知道如何寻求帮助。
最初设计该数据库的人使事情变得比原本应该复杂的多。因此使某些事情难以理解和理解。
为了尝试解释。目前,我正在处理来自TABLE1的数据并将其转换为将要进入TABLE2的新值
UDATE:我最初把桌子摆错了
TABLE1 TABLE2
+---------+---------+---------+ +---------+---------+
| ID | A | B | | ID | C |
+---------+---------+---------+ +---------+---------+
| 01 | DESCRPT1| STRIPED | | 01 | ZEBRA |
+---------+---------+---------+ +---------+---------+
| 01 | DESCRPT2| HORSE | | 02 | SNAKE |
+---------+---------+---------+ +---------+---------+
| 02 | DESCRPT1| SLIMEY |
+---------+---------+---------+
| 02 | DESCRPT2| ROPE |
+---------+---------+---------+
From TABLE1
If Value DESCRPT1 is 'Striped' and DESCIRP2 is 'Horse'
THEN insert 'Zebra' into TABLE2 column C where TABLE1.ID = TABLE2.ID
If Value DESCRPT1 is 'Slimey' and DESCIRP2 is 'Rope'
THEN insert 'Snake' into TABLE2 column C where TABLE1.ID = TABLE2.ID
注意:这是我的第一篇文章,因此,如果我丢失任何信息或做错了。对不起:(
更新
tblEngagementAttributes tblEngagement
+---------+---------+---------+ +---------+---------+---------+
| ID | A | B | | ID | Client | C |
+---------+---------+---------+ +---------+---------+---------+
| 01 | DESCRPT1| STRIPED | | 01 | John | ZEBRA |
+---------+---------+---------+ +---------+---------+---------+
| 01 | DESCRPT2| HORSE | | 02 | Mark | SNAKE |
+---------+---------+---------+ +---------+---------+---------+
| 02 | DESCRPT1| SLIMEY |
+---------+---------+---------+
| 02 | DESCRPT2| ROPE |
+---------+---------+---------+
因此,我发现的更多信息可能有助于解决此问题。.我正在将数据转换为的表已经是一个现有表。这两个表具有共享的外键“ ID”
最佳答案
您可以使用INSERT INTO ... SELECT
构造实现所需的功能。我在这里指出的技巧是在WHERE
上使用TABLE1
子句以将记录限制为仅您肯定希望以某种形式插入的记录。如果省略了WHERE
子句,查询仍将运行,但是您将为不希望映射到TABLE1
的TABLE2
中的任何记录添加虚拟记录。
INSERT INTO TABLE2 (ID, C)
SELECT ID,
CASE WHEN A = 'STRIPED' AND B = 'HORSE' THEN 'ZEBRA'
WHEN A = 'SLIMEY' AND B = 'ROPE' THEN 'SNAKE'
ELSE ''
END
FROM TABLE1
WHERE A = 'STRIPED' AND B = 'HORSE' OR
A = 'SLIMEY' AND B = 'ROPE'
更新:
在您更改之前,我已经对您的原始问题给出了以上答案。对于更新的问题,请尝试以下查询:
INSERT INTO TABLE2 (ID, C)
SELECT t.ID,
CASE WHEN t.ZebraCol >= 2 THEN 'ZEBRA'
WHEN t.SnakeCol >= 2 THEN 'SNAKE'
ELSE ''
END AS C
FROM
(
SELECT ID,
SUM(CASE WHEN (A = 'DESCRPT1' AND B = 'STRIPED') OR
(A = 'DESCRPT2' AND B = 'HORSE') THEN 1 ELSE 0 END) AS ZebraCol,
SUM(CASE WHEN (A = 'DESCRPT1' AND B = 'SLIMEY') OR
(A = 'DESCRPT2' AND B = 'ROPE') THEN 1 ELSE 0 END) AS SnakeCol
FROM TABLE1
GROUP BY ID
) t
WHERE t.ZebraCol >= 2 OR t.SnakeCol >= 2