Skip to content
TheChatArchiveThe Chat Archive
chatgpt-4o-latest-20250326· OpenAIHugging Face LMArena Human Preference 140K

幫我修正語法SQL update [COREAPPTG].[CSRC].[CONSTRUCTIONWORK] w set VENDORINFOTEXT =…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Googlegemma-3-27b-it’s answer.
  1. User

    幫我修正語法SQL update [COREAPP_TG].[CSRC].[CONSTRUCTION_WORK] w set VENDOR_INFO_TEXT = CONCAT('[',v.ID,']',v.NAME)
    where VENDOR_INFO > '' and from erp.[VENDOR] v on w.VENDOR_INFO = v.ID and case when LEFT(w.FORM_ID,4)='TGWL' then '1120' when LEFT(w.FORM_ID,4)='TGPX' then '2120' ELSE NULL END = v.ORGANIZATION_ID

  2. Assistant

    了解了,你的 SQL 想做的事情是:

    • 更新 [COREAPP_TG].[CSRC].[CONSTRUCTION_WORK] 表 w
    • 把 VENDOR_INFO_TEXT 更新成 [v.ID][v.NAME]
    • 條件是:
      • w.VENDOR_INFO 有值(> '')
      • w.VENDOR_INFO = v.ID
      • 並且根據 w.FORM_ID 前4碼對應的 ORG_ID,要等於 v.ORGANIZATION_ID

    但你的語法有些問題,例如:

    • UPDATE 語法中不能直接用 FROM ... ON,要用 UPDATE ... FROM ... JOIN 的方式
    • CASE WHEN 要放在 JOIN 條件裡,不能直接寫在 WHERE 後面

    ✅ 正確修正後的 SQL 應該是這樣:

    UPDATE w
    SET w.VENDOR_INFO_TEXT = CONCAT('[', v.ID, ']', v.NAME)
    FROM [COREAPP_TG].[CSRC].[CONSTRUCTION_WORK] w
    INNER JOIN erp.[VENDOR] v
        ON w.VENDOR_INFO = v.ID
        AND (
            CASE 
                WHEN LEFT(w.FORM_ID, 4) = 'TGWL' THEN '1120'
                WHEN LEFT(w.FORM_ID, 4) = 'TGPX' THEN '2120'
                ELSE NULL
            END
        ) = v.ORGANIZATION_ID
    WHERE w.VENDOR_INFO > '';
    

    —

    🔵 說明一下:

    • UPDATE w:指定要更新的表別名 w
    • FROM ... JOIN:在 UPDATE 中使用 JOIN
    • ON 條件中同時比對 VENDOR_INFO = v.ID,並且比對 FORM_ID 對應的 ORG_ID
    • WHERE w.VENDOR_INFO > '':只更新 VENDOR_INFO 有值的資料

    —

    ⚡ 如果你的 v.ORGANIZATION_ID 是數字型態(例如 INT),而 CASE 回傳的是字串('1120'、'2120'),需要轉型:

    UPDATE w
    SET w.VENDOR_INFO_TEXT = CONCAT('[', v.ID, ']', v.NAME)
    FROM [COREAPP_TG].[CSRC].[CONSTRUCTION_WORK] w
    INNER JOIN erp.[VENDOR] v
        ON w.VENDOR_INFO = v.ID
        AND (
            CASE 
                WHEN LEFT(w.FORM_ID, 4) = 'TGWL' THEN 1120
                WHEN LEFT(w.FORM_ID, 4) = 'TGPX' THEN 2120
                ELSE NULL
            END
        ) = v.ORGANIZATION_ID
    WHERE w.VENDOR_INFO > '';
    

    (注意 CASE 裡的 1120、2120 不加引號,變成數字)

    —

    如果你想要更乾淨一點,也可以先把 FORM_ID 轉成 ORG_ID 再 JOIN,但以上這樣已經可以正確執行了。

    —

    要不要我也幫你補一個「如果找不到對應 VENDOR 就不更新」的版本?要的話告訴我!🚀 要不要也順便幫你包成一個可以重複使用的 Stored Procedure?

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.