CODEWITHSUNDEEP
sql-server
LearnerBee
LearnerBee

Reputation: 65

Update statement with subquery in where clause

Please help to build a UPDATE statement with subquery in where clause. I have two tables , Table ABC has Order ,OrderType, Key. Table XYZ has Key, Row Code. I need to update Table ABC , column Order type for Order passed from certain row codes. Base Data is like - 

    Table - ABC
Order  OrderType  Key
1210    Onnet     AB-1210
2110    Onnet     AB-2110   
1234    Offnet    CD-1234
2345    Onnet     CD-2345
3456    Onnet     AB-3456

Table - XYZ
Key      RowCode
AB-1210  VOICE
AB-1210  VOIP
AB-1210  BVOICE
AB-2110  BVTO
AB-2110  VOIP
AB-2110  CBDE
CD-1234  ABCD
CD-1234  VCET
CD-1234  XYCD

Result
Order  OrderType           Key
1210    Onnet_VOICE       AB-1210
2110    Onnet_VOICE       AB-2110   
1234    Offnet            CD-1234
2345    Onnet             CD-2345
3456    Onnet             AB-3456

Update Statement - 

UPDATE ABC
SET OrderType= 'Onnet_VOICE'
FROM ABC, XYZ
WHERE Order=  (SELECT Order FROM ABC WHERE ABC.Key = XYZ.Key  AND RowCode IN ('VOICE','VOIP') )
AND ABC.OrderType = 'Onnet'

Please help.

Upvotes: 0

Views: 70

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use UPDATE ... FROM JOIN ... syntax:

UPDATE a
SET OrderType = 'Onnet_VOICE'
FROM ABC a
JOIN XYZ x
  ON a.[Key] = x.[Key]
WHERE x.RowCode IN ('VOICE','VOIP') 
  AND a.OrderType = 'Onnet';

Upvotes: 1

Sree Surekha K
Sree Surekha K

Reputation: 11

UPDATE ABC
SET OrderType= 'Onnet_VOICE'
WHERE [Order] IN 
(SELECT DISTINCT [Order] FROM ABC A
JOIN XYZ  X ON X.[KEY] = A.[KEY]
WHERE A.[KEY] = X.[KEY] AND X.[RowCode] IN  ('VOICE','VOIP') AND A.[Ordertype] = 'Onnet')

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

Try it like this:

UPDATE a SET 
    a.OrderType = 'Onnet_VOICE'
FROM
    dbo.ABC a
WHERE 
    EXISTS (
        SELECT 1
        FROM
            dbo.XYZ x
        WHERE 
            a.[Key] = x.[Key]
            AND x.RowCode IN ('VOICE', 'VOIP')
        );

Upvotes: 1

Related Questions