CODEWITHSUNDEEP
sql
Synesthesia
Synesthesia

Reputation: 3

UPDATE a table using a nested SELECT statement in the WHERE clause?

I need to UPDATE a field on a table based on a query ran against that same table.

The value I need to update is the same for all records and essentially I need to update value1 from a 1 to 0. Which I've done on line 2 

SET value1 = '0'

I was attempting to nest a SELECT statement in the WHERE clause to use the values I return from the SELECT statement as a portion of the WHERE clause but receive the following error:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Below is the script I'm using.

UPDATE TABLE1
    SET value1 = '0'
        WHERE EMPLOYID = (SELECT Employid FROM TABLE1 WHERE FIELD1 = 'EXPN' 
and value1='1')
            and FIELD1 = 'EXPN'

What I'm trying to do is set value1 = '0' for any employee that is associated with the field1 value 'EXPN' that currently has a value1='1'

I'm a beginner so I apologize for any incorrect semantics above.

Upvotes: 0

Views: 5162

Answers (3)

Bill Roberts
Bill Roberts

Reputation: 1171

If you want to use a strategy to perform an updates using a subquery, please try this (please also note the change from '=' to 'in'):

UPDATE
 t1
SET
 t1.value1 = '0'
From 
 TABLE1 t1
WHERE
 t1.EMPLOYID in (SELECT t2.Employid FROM TABLE1 t2 WHERE t2.FIELD1 = 'EXPN' and t2.value1='1')
and
 t1.FIELD1 = 'EXPN'

However and as other have noted, you don't need a subquery per your question. This should work just fine, and still give you flexibility to add more tables as needed:

UPDATE
 t1
SET
 t1.value1 = '0'
From 
 TABLE1 t1
WHERE
 t1.FIELD1 = 'EXPN'
and
 t1.value1='1'

Upvotes: 0

Eric Frick
Eric Frick

Reputation: 857

Replace the equal with a IN 

UPDATE TABLE1
    SET value1 = '0'
        WHERE EMPLOYID IN (SELECT Employid FROM TABLE1 WHERE FIELD1 = 'EXPN' 
and value1='1')
            and FIELD1 = 'EXPN'

Upvotes: 0

sorayadragon
sorayadragon

Reputation: 1087

You don't need the subquery here because you're already looking at TABLE1 during the UPDATE. Simply:

UPDATE TABLE1
SET value1 = '0'
WHERE FIELD1 = 'EXPN' AND value1 = '1'

Upvotes: 2

Related Questions