chatgpt-4o-latest-20250326· OpenAI
LMArena Human Preference 140K
幫我修正語法SQL update [COREAPPTG].[CSRC].[CONSTRUCTIONWORK] w set VENDORINFOTEXT =…
- 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 - 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?
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.