Sql-Server

UPDATE 結合嵌套的 SELECT 查詢和 LEFT OUTER JOIN

  • May 7, 2018

我對包含嵌套 SELECT 語句和 LEFT OUTER JOIN 語句的 UPDATE 語句有疑問。

情況：在我們的數據庫中，向我們數據庫中的所有人添加了一個新列，我想在某些人上更新值為“X”的列，但僅限於具有某些條件的人。

通過以下 SELECT 語句，我成功選擇了所有需要更新此列的人。我要更新的列是“Testfield”

SELECT
   TESTDB.CONTACT.number2 AS CustomerID,
   TESTDB.PERSON.firstname AS FirstName,
   TESTDB.PERSON.lastname AS LastName,
   (SELECT UPPER(string19) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) AS Testfield
FROM         
   TESTDB.CONTACT 
   LEFT OUTER JOIN TESTDB.PERSON ON TESTDB.CONTACT.contact_id = TESTDB.PERSON.contact_id
   LEFT OUTER JOIN TESTDB.UDPERSONSMALL ON TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.udpersonSmall_id 
   LEFT OUTER JOIN TESTDB.UDCONTACTLARGE ON TESTDB.CONTACT.userdef2_id = TESTDB.UDCONTACTLARGE.udcontactLarge_id
WHERE 
   (TESTDB.CONTACT.contact_id = TESTDB.PERSON.contact_id) 
/*  No retired people*/
   AND TESTDB.PERSON.retired = '0'
/*  Other conditions*/
   AND ((SELECT UPPER(string05) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'A'
   OR  (SELECT UPPER(string07) FROM CRM5.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'B'
   OR  (SELECT UPPER(string08) FROM CRM5.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'C')
/*  testpersonen*/
   AND (SELECT string45 FROM TESTDB.UDPERSONLARGE WHERE TESTDB.PERSON.userdef2_id = TESTDB.UDPERSONLARGE.UDPERSONLARGE_id) = '123'
ORDER BY
   CustomerID, FirstName, LastName

輸出：

10001;Test1;Person1;
10001;Test2;Person2;
10001;Test3;Person3;
10001;Test4;Person4;
10001;Test6;Person6;
10001;Test7;Person7;
10001;Test8;Person8;

(7 rows affected)

這是正確的，人們目前在 Testfield 上的值為 NULL。（第四欄為空）

現在我想更新“Testfield”列，到目前為止嘗試了這個：

UPDATE
   TESTDB.CONTACT 
   LEFT OUTER JOIN TESTDB.PERSON ON TESTDB.CONTACT.contact_id = TESTDB.PERSON.contact_id
   LEFT OUTER JOIN TESTDB.UDPERSONSMALL ON TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.udpersonSmall_id 
   LEFT OUTER JOIN TESTDB.UDCONTACTLARGE ON TESTDB.CONTACT.userdef2_id = TESTDB.UDCONTACTLARGE.udcontactLarge_id
SET
   (SELECT UPPER(string19) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'J'
WHERE 
   (TESTDB.CONTACT.contact_id = TESTDB.PERSON.contact_id) 
/*  No retired people*/
   AND TESTDB.PERSON.retired = '0'
/*  Other conditions*/
   AND ((SELECT UPPER(string05) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'A'
   OR  (SELECT UPPER(string07) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'B'
   OR  (SELECT UPPER(string08) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'C')
/*  testpersonen*/
   AND (SELECT string45 FROM TESTDB.UDPERSONLARGE WHERE TESTDB.PERSON.userdef2_id = TESTDB.UDPERSONLARGE.UDPERSONLARGE_id) = '123'

但是在這裡我得到了它不接受放置在這裡的 LEFT OUTER JOIN 的錯誤：

Msg 156, Level 15, State 1, Server TESTSERVER, Line 3

Incorrect syntax near the keyword 'LEFT'.

Msg 156, Level 15, State 1, Server TESTSERVER, Line 7

Incorrect syntax near the keyword 'SET'.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 7

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 15

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 16

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 17

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 19

Incorrect syntax near '='.

我也試過這樣：

UPDATE
   TESTDB
SET
   (SELECT UPPER(string19) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'X'
FROM
   TESTDB.CONTACT 
   LEFT OUTER JOIN TESTDB.PERSON ON TESTDB.CONTACT.contact_id = TESTDB.PERSON.contact_id
   LEFT OUTER JOIN TESTDB.UDPERSONSMALL ON TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.udpersonSmall_id 
   LEFT OUTER JOIN TESTDB.UDCONTACTLARGE ON TESTDB.CONTACT.userdef2_id = TESTDB.UDCONTACTLARGE.udcontactLarge_id
WHERE 
   (TESTDB.CONTACT.contact_id = TESTDB.PERSON.contact_id) 
/*  No retired people*/
   AND TESTDB.PERSON.retired = '0'
/*  Other conditions*/
   AND ((SELECT UPPER(string05) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'X'
   OR  (SELECT UPPER(string07) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'X'
   OR  (SELECT UPPER(string08) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) = 'X')
/*  testpersonen*/
   AND (SELECT string45 FROM TESTDB.UDPERSONLARGE WHERE TESTDB.PERSON.userdef2_id = TESTDB.UDPERSONLARGE.UDPERSONLARGE_id) = '123'

這導致另一個錯誤：

Msg 102, Level 15, State 1, Server TESTSERVER, Line 4

Incorrect syntax near '('.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 4

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 17

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 18

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 19

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Server TESTSERVER, Line 21

Incorrect syntax near '='.

該軟體是一個帶有 SQL Server 2005 數據庫的 CRM。

有人可以幫我語法嗎？我要迷路了。

提前致謝。

您的SELECT查詢可以簡化很多，這樣可以更容易地確定需要什麼 UPDATE 語句。所有這些SELECT UPPER都是不必要的，因為您已經加入了它們，所以只需參考該列。

在測試環境中試試這個：

UPDATE c
SET TestField = UPPER(udps.string19)
FROM         
   TESTDB.CONTACT c
   LEFT OUTER JOIN TESTDB.PERSON p ON c.contact_id = p.contact_id
   LEFT OUTER JOIN TESTDB.UDPERSONSMALL udps ON p.userdef_id = udps.udpersonSmall_id 
   LEFT OUTER JOIN TESTDB.UDCONTACTLARGE udc ON c.userdef2_id = udc.udcontactLarge_id
   LEFT OUTER JOIN TESTDB.UDPERSONLARGE udpl ON p.userdef2_id = udpl.UDPERSONLARGE_id
WHERE 
/*  No retired people*/
   p.retired = '0'
/*  Other conditions*/
   AND (UPPER(udps.string05) = 'A'
   OR   UPPER(udps.string07) = 'B'
   OR   UPPER(udps.string08) = 'C')
/*  testpersonen*/
   AND udpl.string45 = '123'

引用自：https://dba.stackexchange.com/questions/205848

相關問答

Sql-Server

使用每行計數 (*) 更新本地表，它是遠端伺服器上內部連接的聚合

  • September 20, 2013
檢視問答
Sql-Server

PostgreSQL 更新連接與 SQL Server 更新連接

  • May 16, 2021
檢視問答
Sql-Server

使用外鍵從映射表中選擇數據

  • April 9, 2021
檢視問答
Mysql

使用另一個表中的數據和 id 更新表元素

  • June 1, 2020
檢視問答
Sql-Server

選擇在另一個表中無法找到所有值的 IDS

  • February 2, 2020
檢視問答
Sql-Server

將 SELECT 結果集的 UPDATE 列更新為 MIN - 出了什麼問題？

  • October 18, 2019
檢視問答