CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-update
NerdFredrick
NerdFredrick

Reputation: 71

how to convert select statement to update statement

I'm unable to convert the select query to the update statement.

SELECT (SELECT DECODE(building_1, 'No', '0', building_1) +
               COALESCE(building_2, '0') + COALESCE(Building_3, '0') +
               COALESCE(Building_4, '0') + COALESCE(Building_5, '0') +
               COALESCE(Building_6, '0') + COALESCE(Building_7, '0')
          FROM tb2
         WHERE marsha_CODE = SUBSTR(OMT.PROPERTY_NAME, 1, 5)) ROOMS
  FROM tb1 ALR, tb3 OMT
 WHERE 1 = 1
   and work_Order_id = OMT.Request_id
   AND ADDRESS = OMT.PROPERTY_ADDRESS

I need below part of the select statement be the set value in update statement 

( SELECT DECODE(building_1, 'No', '0', building_1) +
         COALESCE(building_2, '0') + COALESCE(Building_3, '0') +
         COALESCE(Building_4, '0') + COALESCE(Building_5, '0') +
         COALESCE(Building_6, '0') + COALESCE(Building_7, '0')
    FROM tb2
   WHERE marsha_CODE = SUBSTR(OMT.PROPERTY_NAME, 1, 5) )

and the where clause for the update statement where condition

WHERE 1=1 
  AND work_Order_id = OMT.Request_id 
  AND ADDRESS = OMT.PROPERTY_ADDRESS

Below is what I've tried out:

UPDATE tb1
   SET room_number =
           (SELECT DECODE(building_1, 'No', '0', building_1)
                   + COALESCE (building_2, '0')
                   + COALESCE (Building_3, '0')
                   + COALESCE (Building_4, '0')
                   + COALESCE (Building_5, '0')
                   + COALESCE (Building_6, '0')
                   + COALESCE (Building_7, '0')
              FROM tb2,
                   tb3 OMT
             WHERE marsha_CODE = SUBSTR(OMT.PROPERTY_NAME, 1, 5))
 WHERE work_Order_id =
              (SELECT Request_id
                 FROM tb3
                WHERE tb3.request_id =
                         tb1.WORK_ORDER_ID)
   AND address =
              (SELECT PROPERTY_ADDRESS
                 FROM tb3
                WHERE tb3.PROPERTY_ADDRESS =
                         tb1.ADDRESS)

Upvotes: 1

Views: 107

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use such an Update statement containing with .. as clause :

UPDATE tb1 t
   SET room_number =
       (
        WITH tt as 
        (
        SELECT DECODE(building_1, 'No', '0', building_1) +
               COALESCE(building_2, '0') + COALESCE(Building_3, '0') +
               COALESCE(Building_4, '0') + COALESCE(Building_5, '0') +
               COALESCE(Building_6, '0') + COALESCE(Building_7, '0') as r_number,
               t3.request_id,
               t3.property_address
          FROM tb2 t2
          JOIN tb3 t3
            ON t2.marsha_code = SUBSTR(t3.property_name, 1, 5)
         )
         SELECT tt.r_number
           FROM tt
          WHERE tt.request_id = t.work_order_id
            AND tt.property_address = t.address
       )

even a Merge statement might be used : 

MERGE INTO tb1 t
    USING 
        (
        SELECT DECODE(building_1, 'No', '0', building_1) +
               COALESCE(building_2, '0') + COALESCE(Building_3, '0') +
               COALESCE(Building_4, '0') + COALESCE(Building_5, '0') +
               COALESCE(Building_6, '0') + COALESCE(Building_7, '0') as r_number,
               omt.request_id,
               omt.property_address
          FROM tb2 t2
          JOIN tb3 t3
            ON t2.marsha_code = SUBSTR(t3.property_name, 1, 5)
         ) tt
      ON (tt.request_id = t.work_order_id AND tt.property_address = t.address)
    WHEN MATCHED THEN UPDATE SET t.room_number = tt.r_number;

Upvotes: 1

Related Questions