gpt4 book ai didi

sql - 如何使用条件使用来自单独表的数据更新一个表

转载 作者:行者123 更新时间:2023-12-04 05:38:36 27 4
gpt4 key购买 nike

这个查询可能非常荒谬,或者我只是有一点错误。我不太确定。但基本上我正在尝试使用 Access 中另一个表中的数据更新一个表。给我带来问题的列是那些设置为“是/否”类型的列,我尝试更新它的数据被设置为“文本”类型,其值为字符串"is"或“否”。

所以我对这个查询的想法,如果可能的话,是将“文本”字段粗略地转换为 1 或 0,具体取决于它们的 Yes 或 No 值。无论如何,足够的背景,这里是查询:

UPDATE Group_Pricing SET 
Contract_Type = (SELECT Contract_Type FROM ContractTypesFinal WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID),
Hybrid_Retail =
(SELECT CASE (SELECT RetailSpread FROM ContractTypesFinal WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID)
CASE "Yes"
RetailSpread = 1
CASE "No"
RetailSpread = 0),
Hybrid_Mail =
(SELECT CASE (SELECT MailSpread FROM ContractTypesFinal WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID)
CASE "Yes"
MailSpread = 1
CASE "No"
MailSpread = 0),
Hybrid_Specialty =
(SELECT CASE (SELECT SpecialtySpread FROM ContractTypesFinal WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID)
CASE "Yes"
SpecialtySpread = 1
CASE "No"
SpecialtySpread = 0),
Hybrid_Rebates =
(SELECT CASE (SELECT Rebates FROM ContractTypesFinal WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID)
CASE "Yes"
Rebates = 1
CASE "No"
Rebates = 0)
WHERE EXISTS (SELECT 1 FROM ContractTypesFinal WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID);

非常感谢任何帮助。如果这是不可能的,就告诉我我是个白痴。谢谢!

最佳答案

MS Access 中不能有 CASE,但可以有 IIf。

UPDATE Group_Pricing SET 
Contract_Type =
(SELECT Contract_Type
FROM ContractTypesFinal
WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID),
Hybrid_Retail =
IIf (SELECT RetailSpread
FROM ContractTypesFinal
WHERE ContractTypesFinal.FirstGroup = Group_Pricing.GroupID)="Yes",1,0)

<...>

请注意 MS Access 中的 YesNo 为 -1 和 0

然而,只说:
UPDATE table1 SET AYesNo=(AText="Yes")

编辑
UPDATE Group_Pricing 
INNER JOIN ContractTypesFinal
ON Group_Pricing.GroupID = ContractTypesFinal.FirstGroup
SET Hybrid_Retail = (RetailSpread="Yes")

(RetailSpread="Yes")将评估为 True (-1)

关于sql - 如何使用条件使用来自单独表的数据更新一个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11586974/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com