如何将指定T-SQL UPDATE语句转换为SnowSQL实现？

原T-SQL的逻辑是：对TABLEONE的每一行，匹配TABLETWO中四个字段（TICKETNUMBER、PBS_LOCATOR_ID、TRIPNUMBER、ORIGIN_ID）完全相等的行，随机取其中一行的PBS_LOCATOR_ID来更新TABLEONE的ACS_LOCATION_ID（因为TOP 1无排序定义）。在Snowflake中直接替换TOP 1为LIMIT 1会触发子查询不支持的错误，直接关联多匹配行也会报错，以下是两种可行的实现方式：

方法一：使用CTE+QUALIFY过滤单条匹配行 #

WITH filtered_tabletwo AS (
    SELECT 
        TICKETNUMBER,
        PBS_LOCATOR_ID,
        TRIPNUMBER,
        ORIGIN_ID
    FROM TABLETWO
    -- 按匹配字段分组，每组仅保留任意一行（ORDER BY NULL模拟原TOP 1无排序的随机行为）
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY TICKETNUMBER, PBS_LOCATOR_ID, TRIPNUMBER, ORIGIN_ID 
        ORDER BY NULL
    ) = 1
)
UPDATE TABLEONE
SET ACS_LOCATION_ID = filtered_tabletwo.PBS_LOCATOR_ID
FROM filtered_tabletwo
WHERE 
    filtered_tabletwo.TICKETNUMBER = TABLEONE.TICKETNUMBER
    AND filtered_tabletwo.PBS_LOCATOR_ID = TABLEONE.PBS_LOCATOR_ID
    AND filtered_tabletwo.TRIPNUMBER = TABLEONE.TRIPNUMBER
    AND filtered_tabletwo.ORIGIN_ID = TABLEONE.ORIGIN_ID;

方法二：直接在FROM子句中过滤单条匹配行 #

UPDATE TABLEONE
SET ACS_LOCATION_ID = t2.PBS_LOCATOR_ID
FROM (
    SELECT 
        TICKETNUMBER,
        PBS_LOCATOR_ID,
        TRIPNUMBER,
        ORIGIN_ID
    FROM TABLETWO
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY TICKETNUMBER, PBS_LOCATOR_ID, TRIPNUMBER, ORIGIN_ID 
        ORDER BY NULL
    ) = 1
) t2
WHERE 
    t2.TICKETNUMBER = TABLEONE.TICKETNUMBER
    AND t2.PBS_LOCATOR_ID = TABLEONE.PBS_LOCATOR_ID
    AND t2.TRIPNUMBER = TABLEONE.TRIPNUMBER
    AND t2.ORIGIN_ID = TABLEONE.ORIGIN_ID;

为什么你的尝试写法有问题？ #

• 第一种尝试写法：如果TABLETWO中存在同一匹配键组（四个字段相同）对应多行的情况，Snowflake会抛出Single row subquery returns more than one row错误，因为UPDATE不允许一个目标行对应多个源行的值。
• 第二种尝试写法：子查询(SELECT PBS_LOCATOR_ID FROM TABLETWO LIMIT 1)是从整个TABLETWO中取任意一行的值，并非按TABLEONE每行的匹配条件取对应行，逻辑完全错误，会导致所有匹配的TABLEONE行被更新为同一个固定值。

补充说明 #

如果原T-SQL中TOP 1的实际场景是匹配键组在TABLETWO中本来就只有一行（一对一关联），那可以直接使用你第一种尝试的写法，但需要提前确认数据无多匹配行，否则会报错。

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