幫我修正語法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 語法有一些問題,特別是
from子句的位置不正確。以下是修正後的語法:UPDATE [COREAPP_TG].[CSRC].[CONSTRUCTION_WORK] w SET VENDOR_INFO_TEXT = CONCAT('[', v.ID, ']', v.NAME) FROM [COREAPP_TG].[CSRC].[CONSTRUCTION_WORK] w JOIN erp.[VENDOR] v ON w.VENDOR_INFO = v.ID WHERE w.VENDOR_INFO > '' 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;修正說明:
FROM子句位置:FROM子句應該在WHERE子句之後,並且需要重複寫入w(目標表別名)JOIN子句: 使用JOIN語法明確指定連接條件。CASE語句:CASE語句的結構保持不變,用於根據FORM_ID的前 4 位碼轉換ORGANIZATION_ID。
注意事項:
- 請確認
[COREAPP_TG].[CSRC].[CONSTRUCTION_WORK]和erp.[VENDOR]的表名和 schema 名稱正確。 VENDOR_INFO和ORGANIZATION_ID的資料類型需要相容,才能進行比較。CONCAT函數在某些 SQL Server 版本中可能需要使用+運算符來連接字串。如果CONCAT函數報錯,請嘗試使用+代替。例如:VENDOR_INFO_TEXT = '[' + v.ID + ']' + v.NAME
建議:
- 如果
ORGANIZATION_ID是一個字串類型,請確保CASE語句返回的字串值也用單引號括起來,例如:THEN '1120'。 - 在執行
UPDATE語句之前,建議先使用SELECT語句驗證JOIN和WHERE子句的條件是否正確,以避免意外修改資料。例如:
SELECT w.*, v.* FROM [COREAPP_TG].[CSRC].[CONSTRUCTION_WORK] w JOIN erp.[VENDOR] v ON w.VENDOR_INFO = v.ID WHERE w.VENDOR_INFO > '' 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;
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.