针对特定情况将“选择Oracle查询转换为更新” [英] Convert Select Oracle Query To Updte for specific scenario

问题描述

我们正在使用 Oracle v12 +

我们有一种情况需要更新状态代码首先进行 RENEWAL 时，$ c> 列为先前的值是功能ID，针对一个ID有很多行），否则 ADD 。

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.

请参见下面的示例I / O。数据是根据时间戳为每个ID排序的。

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_book_status

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"}

借助stackOverflow社区，我们能够创建一个选择查询

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
  );

这可以根据需要提供完美的结果，但在选择视图中，我们希望将其转换为 UPDATE oracle查询或 PL / SQL 。对此有任何建议。

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.

推荐答案

从查询更新表的好方法是使用MERGE语句。这是一个复制all_objects并添加与该行所有者和类型匹配的对象数的列的示例。

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
*/

您将[并入您的查询] ON [将查询加入到目标的条件]合并到[目标表]中匹配一行，然后使用查询中的值更新该行。您也可以使用MERGE进行INSERT和DELETE，但是我不会重复所有文档。

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.

这篇关于针对特定情况将“选择Oracle查询转换为更新”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！