CODEWITHSUNDEEP
sql-server
patwary
patwary

Reputation: 153

Subquery is not giving desired result when using in update statement in SQL?

I'm trying to update a column 'validate' in [sdw].[WS_INV_CONTACT] using the below query 

BEGIN TRAN

UPDATE [sdw].[WS_INV_CONTACT]
SET    validate = ( CASE
                      WHEN EXISTS (SELECT A.address_zip_code
                                   FROM   [sdw].[WS_INV_CONTACT] AS A
                                          LEFT JOIN [dbo].[ZipCode] AS B
                                                 ON A.address_zip_code = B.Zip
                                   WHERE  A.address_zip_code = SUBSTRING(address_zip_code, 1, 5)
                                          AND B.zip IS NULL) THEN 'N'
                      ELSE 'Y'
                    END );

Subquery used in above update statement results in 6 rows and I want to update validate column with 'N' only for those six rows, but query is updating all the rows in the table.

Please can anyone suggest me where I'm doing wrong or any better solution.

Highly appreciated

Upvotes: 0

Views: 42

Answers (2)

Martin Smith
Martin Smith

Reputation: 453287

Looks like you need something like the following

UPDATE A
SET    validate = CASE
                    WHEN EXISTS (SELECT *
                                 FROM   [dbo].[ZipCode] AS B
                                 WHERE  B.Zip = LEFT(A.address_zip_code, 5)) THEN 'Y'
                    ELSE 'N'
                  END
FROM   [sdw].[WS_INV_CONTACT] AS A

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Try this

UPDATE a 
SET    validate = ( CASE 
                      WHEN NOT EXISTS (SELECT a.address_zip_code 
                                       FROM   [dbo].[zipcode] AS b 
                                       WHERE  a.address_zip_code = b.zip 
                                              AND a.address_zip_code = 
                                                  Substring(address_zip_code, 1 , 5)) THEN 'N' 
                      ELSE 'Y' 
                    END ) 
FROM   [sdw].[ws_inv_contact] a

Upvotes: 0

Related Questions