diffrence between both query [message #282862] |
Sat, 24 November 2007 04:44  |
|
Hi,
I have run the below queries as per my understanding both query should update the similar data but first query is updating more
number of rows. Could you let me know why it is updating some extra records.there is some diffrence in logic.
begin
dbms_output.put_line('start_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));
update erp_avp_transactions_backup t set
(df_fee_cost)
= (Select cic.item_cost
FROM mf_cmf_item_cost_details cic ,mf_bom_resources br
where 1=1
AND cic.inventory_item_id=t.inventory_item_id
AND cic.organization_id = 1
AND cic.cost_type_id = 1
AND cic.global_name ='US'
and cic.RESOURCE_ID = br.RESOURCE_ID
and cic.COST_ELEMENT_ID = br.COST_ELEMENT_ID
and cic.ROLLUP_SOURCE_TYPE =1
and cic.LEVEL_TYPE =1
and br.RESOURCE_CODE ='DF Fee'
and br.COST_ELEMENT_ID = 3
and br.ORGANIZATION_ID = cic.organization_id
and br.GLOBAL_NAME = 'US'
)
where t.po_line_type='TK Transf'
and t.df_fee_cost is NULL
and t.last_update_date>=TO_DATE('2007-10-28 00:00:00' , 'YYYY-MM-DD HH24:MI:SS');
dbms_output.put_line('end_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('updated df_fee_cost '||sql%rowcount||' rows');
end;
output result-
start_time :24-NOV-2007 00:54:47
end_time :24-NOV-2007 02:09:26
updated df_fee_cost 613421 rows
second query
begin
dbms_output.put_line('start_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));
update erp_avp_transactions_backup t set
(df_fee_cost)
= (Select cic.item_cost
FROM mf_cmf_item_cost_details cic ,mf_bom_resources br
where 1=1
AND cic.inventory_item_id=t.inventory_item_id
AND cic.organization_id = 1
AND cic.cost_type_id = 1
AND cic.global_name ='US'
and cic.RESOURCE_ID = br.RESOURCE_ID
and cic.COST_ELEMENT_ID = br.COST_ELEMENT_ID
and cic.ROLLUP_SOURCE_TYPE =1
and cic.LEVEL_TYPE =1
and br.RESOURCE_CODE ='DF Fee'
and br.COST_ELEMENT_ID = 3
and br.ORGANIZATION_ID = cic.organization_id
and br.GLOBAL_NAME = 'US'
)
where exists
(
Select 1
FROM mf_cmf_item_cost_details cic ,mf_bom_resources br
where 1=1
AND cic.inventory_item_id=t.inventory_item_id
AND cic.organization_id = 1
AND cic.cost_type_id = 1
AND cic.global_name ='US'
and cic.RESOURCE_ID = br.RESOURCE_ID
and cic.COST_ELEMENT_ID = br.COST_ELEMENT_ID
and cic.ROLLUP_SOURCE_TYPE =1
and cic.LEVEL_TYPE =1
and br.RESOURCE_CODE ='DF Fee'
and br.COST_ELEMENT_ID = 3
and br.ORGANIZATION_ID = cic.organization_id
and br.GLOBAL_NAME = 'US'
)
and t.po_line_type='TK Transf'
and t.df_fee_cost is NULL
and t.last_update_date>=TO_DATE('2007-10-28 00:00:00' , 'YYYY-MM-DD HH24:MI:SS');
dbms_output.put_line('end_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('updated df_fee_cost '||sql%rowcount||' rows');
end;
start_time :23-NOV-2007 00:09:42
end_time :23-NOV-2007 02:04:07
updated df_fee_cost 613403 rows
Thanks,
Sagar
|
|
|
Re: diffrence between both query [message #282864 is a reply to message #282862] |
Sat, 24 November 2007 05:07   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Hi,
assuming the only difference is in the WHERE clause (presence of EXISTS clause, the same subquery as in the SET clause), then, yes, the queries may differ in number of updated rows.
In the second example, the query updates only the rows, where the subquery returns a row.
In the first example, it additionally updates the rows, where the subquery does not return any row; such rows in T are updated with NULL value.
Of course, correct behaviour is defined by your requirements and may be any of these two.
|
|
|
|