Convert Update statement to select statement

Question

I'm struggling to convert the following update statement to a select statement. I am hoping you can help provide some tips on how to convert this.

UPDATE inventory_part_tab
SET planner_buyer = (SELECT hb.buyer_code 
                     FROM info.hb_pur_plan_upd1 hb 
                     WHERE hb.part_no = inventory_part_tab.part_no
                       AND hb.contract = inventory_part_tab.contract),
    last_activity_date = DECODE(contract, '01', TO_DATE(SYSDATE), '06', (TO_DATE(SYSDATE) - (3 / 24)), '20', TO_DATE(SYSDATE), '21', TO_DATE(SYSDATE)),
    rowversion = DECODE(contract, '01', SYSDATE, '06', (SYSDATE-(3 / 24)), '20', SYSDATE, '21', SYSDATE, '12', (SYSDATE + (6 / 24)))
WHERE contract IN ('01', '06', '20', '21')
  AND prime_commodity NOT IN ('SPCSL','SPCKT','SPCCC','SPCGK','SPCMT')
  AND planner_buyer <> (SELECT hb.buyer_code 
                        FROM info.hb_pur_plan_upd1 hb 
                        WHERE hb.part_no = inventory_part_tab.part_no
                          AND hb.contract = inventory_part_tab.contract)
  AND EXISTS (SELECT * 
              FROM info.hb_pur_plan_upd1 hb 
              WHERE hb.part_no = inventory_part_tab.part_no 
                AND hb.contract = inventory_part_tab.contract);
COMMIT;

What result columns did you envision the select statement to have? — Andreas, Aug 14 '17 at 18:46

score 0 · Answer 1 · answered Aug 14 '17 at 18:49

To convert an update statement:

UPDATE tbl
   SET a = foo
     , b = bar
 WHERE stuff

to a select statement, do this:

SELECT foo AS a
     , bar AS b
--   , any other column from tbl you want, e.g. an ID
  FROM tbl
 WHERE stuff

It's really rather simple.

score 0 · Accepted Answer · answered Aug 14 '17 at 18:50

0

UPDATE <TABLENAME>
SET <fieldName> = <value>
WHERE <conditons>

Would be

SELECT (<value>) as <fieldName
FROM <TABLENAME>
WHERE <conditons>

In this case <value> is a big subquery but works the same, just add the parenthesis.

answered Aug 14 '17 at 18:50

Juan Carlos Oropeza

47,252
12
78
118

Convert Update statement to select statement

2 Answers2