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.