Sql-server – UPDATE in combination with nested SELECT queries and LEFT OUTER JOIN

join;selectsql serversql-server-2005update

I have a problem with an UPDATE statement that contains a nested SELECT statement in combination with LEFT OUTER JOIN statements.

Situation: In our database there is a new column added to all people who are in our database, and I want to update the column with the value "X" on certain people, but only to the people that have certain conditions.

With the following SELECT statement I successfully select all people that need to have this column updated. The column that I want to update is " 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

Output:

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

(7 rows affected)

This is correct, the people have the value NULL on the Testfield at the moment. (the fourth column is empty)

Now I want to update the column "Testfield", and tried this so far:

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'

But here I get the error that it doesn't accept the LEFT OUTER JOIN placed here:

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 '='.

I also tried it like this:

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'

This results in another error:

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 '='.

The software is a CRM with a SQL Server 2005 database.

Could someone help me with the syntax? I'm getting lost.

Thanks in advance.

Best Answer

Your SELECT query can be simplified quite a bit, which would make it easier to figure out what UPDATE statement is needed. All of those SELECT UPPER are unnecessary as you've already joined to them, so just reference the column.

Try this in a test environment:

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'
Related Question