创建一个名为TEST的表，包含3列.2列有大查询作为单元格值.我想在oracle sql中使用reg表达式更新列数据.&

Question

~Test_num  ~src_qry      ~tgt qry
---------------------------------------
~123       ~sql query1    ~sql_query2
~124       ~sql query3    ~sql query6
~125       ~sql query4    ~sql query7
~126       ~sql query5    ~sql query8

sql query:这是一个Oracle sql查询(测试存储在表中的查询)，用于测试表中的数据(连接，子查询)。例如:customer, relation, transaction and partition_table被使用)

change:如果在查询src_qry和TGT qry列时使用了partition_table，那么应该从查询中删除partition_table.frequency列条件from。

输出:作为一个事实我想检查哪个记录使用了partition_table.frequency列，如果列在src_qry和tgt qry列中使用，那么我需要删除该条件数据(通过删除frequency='D'或frequency='M')。

之前更新(示例):

select c1,c2 from T1
leftjoin t2 on t2.c1=t1.c1
and t2.part_num=(select part_num from *abc.partition_table pt where pt.frequency='M' and type_code='cp'* )
where t1.c3='xyz'
and t1.part_num=(select part_num from *xyz.partition_table pm where type_code='cp' and pm.frequency='M'*);

更新后:

select c1,c2 from T1
leftjoin t2 on t2.c1=t1.c1
and t2.part_num=(select part_num from **abc.partition_table pt where  and type_code='cp'**)
where t1.c3='xyz'
and t1.part_num=(select part_num from **xyz.partition_table pm where type_code='cp'**);

如何处理这种更新

有谁能帮帮我吗?

提前感谢。

Answer 1

我想用代替1=1

的条件

• 它总是为真

• 你不必担心要替换的字符串前面是什么(是where还是and，如果你只是"删除"这些条件，例如，你可能会得到

  where and type_code='cp'
  where type_code='cp' and);
  

  都是无效的

下面是一个例子:

SQL> with test (col) as
2    (select q'[
3  select c1,c2 from T1
4  left join t2 on t2.c1=t1.c1
5  and t2.part_num=(select part_num from abc.partition_table pt where pt.frequency='M' and type_code='cp' )
6  where t1.c3='xyz'
7  and t1.part_num=(select part_num from xyz.partition_table pm where type_code='cp' and pm.frequency='M');]'
8     from dual
9    )
10  select regexp_replace(col, q'[p..frequency='M'|'D']', '1=1') result
11  from test
12  where instr(col, '.frequency') > 0;
RESULT
----------------------------------------------------------------------------------------------------
select c1,c2 from T1
left join t2 on t2.c1=t1.c1
and t2.part_num=(select part_num from abc.partition_table pt where 1=1 and type_code='cp' )
where t1.c3='xyz'
and t1.part_num=(select part_num from xyz.partition_table pm where type_code='cp' and 1=1);

SQL>