I'm hoping to clear up some confusion about updating table data and when Where Exists should be used. If I'm updating a table when reference to itself, is the only requirement to match on the constraint data? Here's an example, why is the Where Exists needed?
create table condition_survey (eff_year VARCHAR2(4), eff_date date, custom_sel_2 VARCHAR2(4), distrank NUMBER(3.0), corank NUMBER(3.0), projectno VARCHAR2(22), from_point NUMBER(8,3), to_point NUMBER(8,3), lane_dir VARCHAR2(1));
insert into TEST_condition_survey values ('2017', '24-JUL-14', '002', 3,null, 'FD05-054-0336-000-002', 0, 1.388, '*');
insert into TEST_condition_survey values ('2017', '24-JUL-14', '002', 2,null, 'FD05-054-1581-000-001', 0, 0.888, '*');
insert into TEST_condition_survey values ('2017', '01-JUN-15', '002', 1,null, 'FD05-054-1178-001-003', 1.57, 2.134, '*');
UPDATE condition_survey a
SET a.corank=
(SELECT distrank
FROM condition_survey b
WHERE b.eff_year ='2017'
AND b.custom_sel_2 ='002'
AND b.distrank IS NOT NULL
AND a.projectno =b.projectno
AND a.from_point =b.from_point
AND a.to_point =b.to_point
and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
AND a.eff_year =b.eff_year
AND a.eff_date =b.eff_date
)
WHERE EXISTS
(SELECT *
FROM condition_survey b
WHERE b.eff_year ='2017'
AND b.custom_sel_2 ='002'
AND b.distrank IS NOT NULL
AND a.projectno =b.projectno
AND a.from_point =b.from_point
AND a.to_point =b.to_point
and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
AND a.eff_year =b.eff_year
AND a.eff_date =b.eff_date
) ;
I know in this case the Where Exists section is needed, otherwise CORANK for my entire table is set to null for any record that doesn't match my subquery. Why does that happen? Also, would this be just as effective?
UPDATE condition_survey a
SET a.corank=
(SELECT distrank
FROM condition_survey b
WHERE b.eff_year ='2017'
AND b.custom_sel_2 ='002'
AND b.distrank IS NOT NULL
AND a.projectno =b.projectno
AND a.from_point =b.from_point
AND a.to_point =b.to_point
and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
AND a.eff_year =b.eff_year
AND a.eff_date =b.eff_date
)
Where a.eff_year ='2017'
AND a.custom_sel_2 ='002'
AND a.distrank IS NOT NULL
OR This?
update
(select eff_year, eff_date, custom_sel_2, distrank, corank, projectno, from_point, to_point, lane_dir
from condition_survey
where eff_year=2017 and custom_sel_2='002'
and distrank is not null
) a
SET a.corank=
(SELECT distrank
FROM condition_survey b
WHERE b.eff_year ='2017'
AND b.custom_sel_2 ='002'
AND b.distrank IS NOT NULL
AND a.projectno =b.projectno
AND a.from_point =b.from_point
AND a.to_point =b.to_point
and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
AND a.eff_year =b.eff_year
AND a.eff_date =b.eff_date
)
Is there any reason to use the first method vs the second or third? All of these work, unless I've typed something incorrectly. Are all methods valid and just a point of preference, or have I just gotten lucky?
They are equivalent as long as you are taking your nulls carefully into account), eg, in the table definition you provided, projectno is nullable (which might just be because its your test case), but if that is a genuine reflection, then this could happen:
SQL>
SQL> drop table condition_survey purge;
Table dropped.
SQL>
SQL> create table condition_survey (eff_year VARCHAR2(4), eff_date date,
2 custom_sel_2 VARCHAR2(4), distrank NUMBER(3.0), corank NUMBER(3.0), projectno
3 VARCHAR2(22), from_point NUMBER(8,3), to_point NUMBER(8,3), lane_dir
4 VARCHAR2(1));
Table created.
SQL>
SQL> insert into condition_survey values ('2017', '24-JUL-14', '002', 3,null, 'FD05-054-0336-000-002', 0, 1.388, '*');
1 row created.
SQL> insert into condition_survey values ('2017', '24-JUL-14', '002', 2,null, 'FD05-054-1581-000-001', 0, 0.888, '*');
1 row created.
SQL> insert into condition_survey values ('2017', '01-JUN-15', '002', 1,null, 'FD05-054-1178-001-003', 1.57, 2.134, '*');
1 row created.
SQL> insert into condition_survey values ('2017', '11-JUN-15', '002', 4,null, null, 1.57, 2.134, '*');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> UPDATE condition_survey a
2 SET a.corank=
3 (SELECT distrank
4 FROM condition_survey b
5 WHERE b.eff_year ='2017'
6 AND b.custom_sel_2 ='002'
7 AND b.distrank IS NOT NULL
8 AND a.projectno =b.projectno
9 AND a.from_point =b.from_point
10 AND a.to_point =b.to_point
11 and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
12 AND a.eff_year =b.eff_year
13 AND a.eff_date =b.eff_date
14 )
15 WHERE EXISTS
16 (SELECT *
17 FROM condition_survey b
18 WHERE b.eff_year ='2017'
19 AND b.custom_sel_2 ='002'
20 AND b.distrank IS NOT NULL
21 AND a.projectno =b.projectno
22 AND a.from_point =b.from_point
23 AND a.to_point =b.to_point
24 and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
25 AND a.eff_year =b.eff_year
26 AND a.eff_date =b.eff_date
27 ) ;
3 rows updated.
SQL>
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL>
SQL> UPDATE condition_survey a
2 SET a.corank=
3 (SELECT distrank
4 FROM condition_survey b
5 WHERE b.eff_year ='2017'
6 AND b.custom_sel_2 ='002'
7 AND b.distrank IS NOT NULL
8 AND a.projectno =b.projectno
9 AND a.from_point =b.from_point
10 AND a.to_point =b.to_point
11 and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
12 AND a.eff_year =b.eff_year
13 AND a.eff_date =b.eff_date
14 )
15 Where a.eff_year ='2017'
16 AND a.custom_sel_2 ='002'
17 AND a.distrank IS NOT NULL;
4 rows updated.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> update
2 (select eff_year, eff_date, custom_sel_2, distrank, corank, projectno,
3 from_point, to_point, lane_dir
4 from condition_survey
5 where eff_year=2017 and custom_sel_2='002'
6 and distrank is not null
7 ) a
8 SET a.corank=
9 (SELECT distrank
10 FROM condition_survey b
11 WHERE b.eff_year ='2017'
12 AND b.custom_sel_2 ='002'
13 AND b.distrank IS NOT NULL
14 AND a.projectno =b.projectno
15 AND a.from_point =b.from_point
16 AND a.to_point =b.to_point
17 and (a.lane_dir=b.lane_dir or (a.lane_dir is null and b.lane_dir is null))
18 AND a.eff_year =b.eff_year
19 AND a.eff_date =b.eff_date
20 );
4 rows updated.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL>
But assuming this is just an anomaly of the test case you provided, then yes, you can pick the one that is most efficient for you.
And dont forget the additional option of using MERGE (because the WHEN-NOT-MATCHED is optional in recent releases)