2

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.

Improve this question
1
  • Also, note that you can't SET the result of a SELECT statement to a value; you have to set a specific column to the value you want.
    – RDFozz
    May 4, 2018 at 15:07

2 Answers 2

Reset to default
4

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'
Improve this answer
3
  • Thanks, only one thing, how can I put value "J" into TestField now as you seem to use the SET clause to compare TestField with UPPER(udps.string19), instead of putting the value 'J' in TestField (which is UPPER(udps.string19)) ?
    – WickedNet
    May 7, 2018 at 8:14
  • Ok, I changed "TestField = UPPER(udps.string19)" to "udps.string19 = 'J'" now. I'm going to try it in a minute.
    – WickedNet
    May 7, 2018 at 9:25
  • It worked with a small edit! I changed the top 2 lines to: UPDATE udps SET udps.string19 = 'J' That did it. Thanks for your help, really cleared it up for me!
    – WickedNet
    May 7, 2018 at 10:52
2

See if this helps:

Update TESTDB.CONTACT 
SET Testfield = SubTestField
FROM
(
SELECT
    TESTDB.CONTACT.number2 AS CustomerID,
    TESTDB.PERSON.firstname AS FirstName,
    TESTDB.PERSON.lastname AS LastName,
    TESTDB.CONTACT.contact_id AS SubContact_ID
    (SELECT UPPER(string19) FROM TESTDB.UDPERSONSMALL WHERE TESTDB.PERSON.userdef_id = TESTDB.UDPERSONSMALL.UDPERSONSMALL_id) AS SubTestfield
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'
    )Sub
Where TESTDB.CONTACT.contact_id = SubContact_ID

Your set clause needs to have a value to compare to. See this answer by Robin Day for another example. I will also post the example below for viewing ease.

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.