Access SQL插入需求：基于其他表Ticker匹配设置字段值

嗨，作为Access SQL新手，你的需求完全可以通过左连接结合条件判断来实现，咱一步一步来拆解并写出正确的SQL语句：

核心思路 #

1. 从[Excel Monthly Detail]表获取所有需要插入的Ticker
2. 通过左连接关联[Model Detail]表，判断当前Ticker是否存在，以此设置[Current Model]为"Y"或"N"
3. 同样通过左连接关联[Excel Account Performance]表，设置[Current Account]字段值

完整SQL语句 #

INSERT INTO [Current Model/Account Tickers] (Ticker, [Current Model], [Current Account])
SELECT 
    emd.Ticker,
    IIF(md.Ticker IS NOT NULL, "Y", "N") AS [Current Model],
    IIF(eap.Ticker IS NOT NULL, "Y", "N") AS [Current Account]
FROM 
    [Excel Monthly Detail] emd
LEFT JOIN 
    [Model Detail] md ON emd.Ticker = md.Ticker
LEFT JOIN 
    [Excel Account Performance] eap ON emd.Ticker = eap.Ticker;

关键细节说明 #

• LEFT JOIN的作用：确保[Excel Monthly Detail]中的每一行都会被保留，哪怕在另外两个表中找不到匹配的Ticker
• IIF函数判断：如果左连接后对应的Ticker字段不为空，说明存在匹配，就设为"Y"，否则设为"N"
• 表别名：给表起了emd、md、eap的别名，让SQL语句更简洁易读

可选优化：避免重复插入 #

如果担心目标表中已经存在相同的Ticker，可以在SELECT语句后加上WHERE NOT EXISTS来过滤：

INSERT INTO [Current Model/Account Tickers] (Ticker, [Current Model], [Current Account])
SELECT 
    emd.Ticker,
    IIF(md.Ticker IS NOT NULL, "Y", "N") AS [Current Model],
    IIF(eap.Ticker IS NOT NULL, "Y", "N") AS [Current Account]
FROM 
    [Excel Monthly Detail] emd
LEFT JOIN 
    [Model Detail] md ON emd.Ticker = md.Ticker
LEFT JOIN 
    [Excel Account Performance] eap ON emd.Ticker = eap.Ticker
WHERE NOT EXISTS (
    SELECT 1 FROM [Current Model/Account Tickers] cm
    WHERE cm.Ticker = emd.Ticker
);

内容的提问来源于stack exchange，提问作者LEBoyd