Convert Select Oracle Query To Updte for specific scenario

Question

we are using Oracle v12+

we have a situation where we need to update status column to previous value whenever we have first RENEWAL for every code (it is functional ID and has many rows against one ID) otherwise ADD.

See sample i/o below. Data is sorted by timestamp for each ID.

Do we need specific joins to update? I have copied data to temp table for that but not getting success.

Table name: table_book_status

     Input                           CLOB
    [code]        [word]         [status]                               [timestamp]
    B000JMLBHU  book    {"name" : "Kunal", "type" : "RENEWAL"}
    B000JMLBHU  read    {"name" : "Kunal", "type" : "RENEWAL"}
    B000JMLBHU  was     {"name" : "Kunal", "type" : "MODIFY"}
    B000JMLBHU  story   {"name" : "Kunal", "type" : "ADD"}
    B000R93D4Y  with    {"name" : "RAHUL", "type" : "RENEWAL"}
    B000R93D4Y  book    {"name" : "RAHUL", "type" : "RENEWAL"}
    B000R93D4Y  story   {"name" : "RAHUL", "type" : "RENEWAL"}
    B000R93D4Y  was     {"name" : "RAHUL", "type" : "MODIFY"}
    B000R93D4Y  have    {"name" : "RAHUL", "type" : "ADD"}
    B001892DGG  was     {"name" : "Kanav", "type" : "ADD"}
    B001892DWA  was     {"name" : "Kavita", "type" : "ADD"}
    B001BXNQ2O  was     {"name" : "Keshav", "type" : "RENEWAL"}
    B001BXNQ2O  book    {"name" : "Keshav", "type" : "RENEWAL"}
    B001H55R8M  was     {"name" : "Raghav", "type" : "MODIFY"}
    B001HQHCBQ  was     {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  story   {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  bella   {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  with    {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  love    {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  zsadist {"name" : "RINA", "type" : "ADD"}


    Output


   [code]        [word]              [status]                               [timestamp]
    B000JMLBHU  book    {"name" : "Kunal", "type" : "RENEWAL"}
    B000JMLBHU  read    {"name" : "Kunal", "type" : "**MODIFY**"}
    B000JMLBHU  was     {"name" : "Kunal", "type" : "MODIFY"}
    B000JMLBHU  story   {"name" : "Kunal", "type" : "ADD"}
    B000R93D4Y  with    {"name" : "RAHUL", "type" : "RENEWAL"}
    B000R93D4Y  book    {"name" : "RAHUL", "type" : "RENEWAL"}
    B000R93D4Y  story   {"name" : "RAHUL", "type" : "**MODIFY**"}
    B000R93D4Y  was     {"name" : "RAHUL", "type" : "MODIFY"}
    B000R93D4Y  have    {"name" : "RAHUL", "type" : "ADD"}
    B001892DGG  was     {"name" : "Kanav", "type" : "ADD"}
    B001892DWA  was     {"name" : "Kavita", "type" : "ADD"}
    B001BXNQ2O  was     {"name" : "Keshav", "type" : "RENEWAL"}
    B001BXNQ2O  book    {"name" : "Keshav", "type" : "**ADD**"}
    B001H55R8M  was     {"name" : "Raghav", "type" : "MODIFY"}
    B001HQHCBQ  was     {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  story   {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  bella   {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  with    {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  love    {"name" : "RINA", "type" : "ADD"}
    B001HQHCBQ  zsadist {"name" : "RINA", "type" : "ADD"}

With the help of stackOverflow community we were able to create a select query

    SELECT code,
  status,
  CASE
    WHEN sm    = 1
    AND status = 'RENEWAL'
    THEN COALESCE(lgst, 'ADD')
    ELSE status
  END AS status1,
  timestamp
FROM
  (SELECT code,
    JSON_VALUE(status, '$.type') AS status,
    SUM(
    CASE
      WHEN JSON_VALUE(status, '$.type') = 'RENEWAL'
      THEN 1
      ELSE 0
    END) over (partition BY code order by timestamp)                                              AS sm,
    lag(JSON_VALUE(status, '$.type')) over (partition BY code order by timestamp) AS lgst,
    timestamp
  FROM table_book_status
  );

This is giving perfect result as needed but in select view, we want to get that converted to UPDATE oracle query or maybe PL/SQL. Any suggestion on that.

Answer 1

A great way to update a table from a query is to use a MERGE statement. Here is an example that copies all_objects and adds a column of the number of objects that match that rows owner and type.

CREATE TABLE all_objects_2 AS
SELECT *
  FROM all_objects;

ALTER TABLE all_objects_2 add owner_and_type_ct NUMBER;

MERGE INTO all_objects_2 ao2
USING (SELECT ao.owner,
              ao.object_type,
              COUNT(*) AS ct
         FROM all_objects ao
        GROUP BY ao.owner,
                 ao.object_type) x
ON (ao2.owner = x.owner AND ao2.object_type = x.object_type)
WHEN MATCHED THEN
  UPDATE
     SET ao2.owner_and_type_ct = x.ct;

SELECT ao2.owner,
       ao2.object_name,
       ao2.object_type,
       ao2.owner_and_type_ct
  FROM all_objects_2 ao2
 WHERE rownum < 10;
/*
SYS C_TS# CLUSTER 10
SYS I_TS# INDEX 1459
SYS C_FILE#_BLOCK#  CLUSTER 10
SYS I_FILE#_BLOCK#  INDEX 1459
SYS FET$  TABLE 1592
SYS UET$  TABLE 1592
SYS SEG$  TABLE 1592
SYS UNDO$ TABLE 1592
SYS TS$ TABLE 1592
*/

You MERGE INTO [destination table] USING [your query] ON [criteria to join query to destination] and WHEN you MATCH a row you UPDATE the row with the values from the query. You can also INSERT and DELETE with a MERGE, but I'm not going to duplicate all the documentation.