Oracle Multiple更新查询 [英] Oracle Multiple update Query
问题描述
我的数据库中有两个表，如下表所示.在每个表DETEM_MENT_CODE的第一个ITEM表中，将有多个ITEM_CODE.
I have two tables like the below one in my DB. In the first table ITEM for evry DEPARTMENT_CODE there will be multiple ITEM_CODE.
ITEM
----------------------------------------------------
"STORE_CODE" "ITEM_CODE" "DEPARTMENT_CODE"
"011" "912003" "14"
"011" "912004" "14"
"011" "914001" "14"
----------------------------------------------------
COMPETITOR
--------------------------------------------------------------
"STORE_CODE" "ITEM_CODE" "DEPARTMENT_CODE" "COMPETITOR_CODE"
"011" "912003" "14" "01"
"011" "912003" "14" "02"
"011" "912003" "14" "03"
"011" "912004" "14" "01"
"011" "912004" "14" "02"
"011" "912004" "14" "04"
"011" "914001" "14" "01"
"011" "914001" "14" "02"
"011" "914001" "14" "03"
-------------------------------------------------------------
在表COMPETITOR evey ITEMCODE中将有三个条目，并且具有不同的competitor_Code
In the Table COMPETITOR evey ITEMCODE will have three entries and have different competitor_Code for that
我有三个值Comp_1，comp_2，comp_3和department_code = 14;
I have three values Comp_1, comp_2, comp_3 and department_code =14;
我想做的是用comp_1，comp_2，comp_3更新COMPETITOR表，以获取项目表中Department_code为14的evry Item_code
what i want to do is update COMPETITOR table with comp_1, comp_2, comp_3 for evry Item_code which has department_code as 14 in ITEM table
样本输出
COMPETITOR
--------------------------------------------------------------
"STORE_CODE" "ITEM_CODE" "DEPARTMENT_CODE" "COMPETITOR_CODE"
"011" "912003" "14" "Comp_1"
"011" "912003" "14" "Comp_2"
"011" "912003" "14" "Comp_3"
"011" "912004" "14" "Comp_1"
"011" "912004" "14" "Comp_2"
"011" "912004" "14" "Comp_3"
"011" "914001" "14" "Comp_1"
"011" "914001" "14" "Comp_2"
"011" "914001" "14" "Comp_3"
-------------------------------------------------------------
如何为此编写单个oracle查询?
How can i write a single oracle query for this??
推荐答案
以下假定ITEM_CODE仅分配给一个DEPARTMENT_CODE，因为它很简单，并且您没有给我们进一步的业务规则.如果这个假设是错误的，那么您将需要相应地调整逻辑.
The following assumes that an ITEM_CODE is assigned to only one DEPARTMENT_CODE, because it's simple and you haven't given us any further business rules. It this assumption is wrong you will need to adjust the logic accordingly.
我还结合了您的要求，即COMPETITOT.COMPETITOR_CODE的现有值不可靠.
I have also incorporated your requirement that the existing value of COMPETITOT.COMPETITOR_CODE is unreliable.
给出这个测试日期:
SQL> select * from competitor
2 /
STORE_CODE ITEM_CODE DEPARTMENT_CODE COMPETITOR
---------- ---------- --------------- ----------
11 912003 14 01
11 912003 14 04
11 912003 14 03
11 912004 14 01
11 912004 14 02
11 912004 14 04
11 914001 14 01
11 914001 14 02
11 914001 14 05
9 rows selected.
SQL>
我们可以使用解析ROW_NUMBER()生成COMPETITOR_CODE的必要句柄:
We can use an analytic ROW_NUMBER() to generate the necessary handle for COMPETITOR_CODE:
SQL> update competitor c
2 set competitor_code =
3 (select decode (dr
4 , 1, 'Comp_1'
5 , 2, 'Comp_2'
6 , 3, 'Comp_3')
7 from ( select row_number() over ( partition by x.item_code
8 order by x.rowid ) as dr
9 , x.rowid as row_id
10 from competitor x
11 where x.item_code in ( select item_code
12 from item
13 where department_code = 14 ) ) l
14 where c.rowid = l.rowid )
15 /
9 rows updated.
SQL>
这是理想的结果(除非进一步添加业务规则):
And this is the desired result (barring any further additions to the business rules):
SQL> select * from competitor
2 /
STORE_CODE ITEM_CODE DEPARTMENT_CODE COMPETITOR
---------- ---------- --------------- ----------
11 912003 14 Comp_1
11 912003 14 Comp_2
11 912003 14 Comp_3
11 912004 14 Comp_1
11 912004 14 Comp_2
11 912004 14 Comp_3
11 914001 14 Comp_1
11 914001 14 Comp_2
11 914001 14 Comp_3
9 rows selected.
SQL>
这篇关于Oracle Multiple更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
