I have a table T(col1, col2, col3, col4) and I run sub-queries on it that results on two disjunct sets of data. Now I have to update col2 from the first set of data to the values from col3 of the second subquery.
How should the update query be?
I tried something like
update subset_d set subset_d.col2=subset_c.col3
where d.col1=c.col1 and
d.col4=d.col4
The subset_c and subset_d are run on the same table
In my situation the table HORATIU_NETWORK_AREA and the subqueries are d and c. The query I tried is -
-
update d set d.eff_stop_ts= c.eff_start_ts from
-
(
-
select a.NETWORK_AREA_ID, a.CELL_ID, a.LAC_ID, to_char(a.EFF_STOP_TS,'dd-mm-yyyy hh:mi:ss'), a.TIME_ZONE_CD, a.BID, to_char(a.EFF_START_TS,'dd-mm-yyyy hh:mi:ss'), a.NETWORK_AREA_PROV_STATE_CD, a.NETWORK_AREA_CITY_NM, a.SWITCH_ID, a.CREATE_USER_ID,a.LAST_UPDT_USER_ID, to_char(a.CREATE_TS,'dd-mm-yyyy hh:mi:ss'), to_char(a.LAST_UPDT_TS,'dd-mm-yyyy hh:mi:ss')
-
from
-
HORATIU_NETWORK_AREA
-
-
a,
-
(
-
select hna1.CELL_ID, hna1.LAC_ID, count(*) from
-
HORATIU_NETWORK_AREA hna1
-
-
group by hna1.CELL_ID,hna1.LAC_ID having count(*)>1
-
) b
-
where
-
a.cell_id = b.cell_id and
-
a.lac_id=b.lac_id and
-
a.eff_stop_ts >= to_date('01-01-2029 00:00:00', 'dd-mm-yyyy hh24:mi:ss') and
-
a.eff_stop_ts <= to_date('01-01-9999 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
-
order by a.cell_id, a.lac_id
-
) d,
-
(
-
select a.NETWORK_AREA_ID, a.CELL_ID, a.LAC_ID, to_char(a.EFF_STOP_TS,'dd-mm-yyyy hh:mi:ss'), a.TIME_ZONE_CD, a.BID, to_char(a.EFF_START_TS,'dd-mm-yyyy hh:mi:ss'), a.NETWORK_AREA_PROV_STATE_CD, a.NETWORK_AREA_CITY_NM, a.SWITCH_ID, a.CREATE_USER_ID,a.LAST_UPDT_USER_ID, to_char(a.CREATE_TS,'dd-mm-yyyy hh:mi:ss'), to_char(a.LAST_UPDT_TS,'dd-mm-yyyy hh:mi:ss')
-
from HORATIU_NETWORK_AREA a,
-
(
-
select hna1.CELL_ID, hna1.LAC_ID, count(*) from
-
HORATIU_NETWORK_AREA hna1
-
-
group by hna1.CELL_ID,hna1.LAC_ID having count(*)>1) b
-
where
-
a.cell_id = b.cell_id and
-
a.lac_id=b.lac_id and
-
a.eff_stop_ts >= to_date('01-01-9999 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
-
order by a.cell_id, a.lac_id
-
) c
-
where
-
c.cell_id = d.cell_id and
-
c.lac_id = c.lac_id
-
.lac_id=d.lac_id
-
|
