Database / SQL question – this is hard?
I have two tables that use either three or four logical keys to determine duplication. I need to populate the 3-key table with the 4-key’s unique field. The keys map like this
3-Key
ABC
4-Key
ABCD
I need to get D, put it into 3-key, when there’s a match based on ABC. Here’s the catch. ABC are allowed to be equal on both tables, and D is the only real designator or uniqueness on the 4-key. Examples:
4-Key
A B C D
ALGEBRA 12349999 X7
ALGEBRA 12349999 A2
ALGEBRA 12349999 P6
ALGEBRA 12349996 F8
ALGEBRA 12349199 K1
ALGEBRA 12349299 E2
ALGEBRA 12349013 C9
ALGEBRA 12349988 Z5
3 Key
A B C
ALGEBRA 12349999
ALGEBRA 12349999
ALGEBRA 12349999
ALGEBRA 12349996
ALGEBRA 12349199
ALGEBRA 12349299
ALGEBRA 12349013
ALGEBRA 12349988
The problem – when I try joining the two tables I get the "ORA-01427: single-row subquery returns more than one row" because it doesn’t know how to handle the first three rows in 3-Key (which D do I take?)
How can I make it so that it fills in all the rows after the first three, in other words, fill in when the query returns one row, and do nothing but don’t fail if it returns more than one?
My code:
update grade_table b
set b.D =
(select distinct
a.D from local_college_course a
where
trim(b.A||b.B||b.C)=trim(a.A||a.B||a.C)
and a.D is not null
group by a.D
having count(distinct a.D) = 1
)
where exists (select ‘x’ from local_college_course a
where
trim(b.A||b.B||b.C)=trim(a.A||a.B||a.C)
and a.D is not null)
I replaced the actual column names with ABCD to save space. I need it to look like this
3-Key
A B C D
ALGEBRA 12349999
ALGEBRA 12349999
ALGEBRA 12349999
ALGEBRA 12349996 F8
ALGEBRA 12349199 K1
ALGEBRA 12349299 E2
ALGEBRA 12349013 C9
ALGEBRA 12349988 Z5
I can’t just dedupe the 4-key table because there are lot of fields I need to maintain on it.
tried your suggestion. Its not identifying the columns in the second subquery –
select * from local_college_course l, ( <insert Query 1> ) k
where k.A=l.A and k.B=l.B and k.C=l.C
ERROR at line 5:
ORA-00904: "L"."C": invalid identifier
both excellent answers, but I’m having better luck with techieguys. I’m reduced to this error:
ORA-00904: "T1"."C": invalid identifier
its in ref to line 15: WHERE t.a = t1.a AND t.b = t1.b AND t.c = t1.c)
any thoughts on what I’m doing wrong?
Oy gevalt!
Ok, you can do this with multiple subqueries:
First you’d trim 4 Key to the lines with one D value per unique ABC:
(I’m not checkign syntax on this stuff):
Query1
—————–
select A, B,C , count (*)
from local_college_course
group by A,B,C
having count (*) =1
You actually have ‘distinct D’ in your ‘having’. Who designed this table and why do they hate you?
Next you’d rejoin that with original local_college_course:
Query 2
——————-
select * from local_college_course l, ( <insert Query 1> ) k
where k.A=l.A and k.B=l.B and k.C=l.C
and then use THAT to join with grade_table:
update grade_table b
set b.D= ( select D from ( <insert QUERY 2> ) where trim ( yadda yadda yadda ….
You get the idea.
Good luck.
Oy gevalt!
Ok, you can do this with multiple subqueries:
First you’d trim 4 Key to the lines with one D value per unique ABC:
(I’m not checkign syntax on this stuff):
Query1
—————–
select A, B,C , count (*)
from local_college_course
group by A,B,C
having count (*) =1
You actually have ‘distinct D’ in your ‘having’. Who designed this table and why do they hate you?
Next you’d rejoin that with original local_college_course:
Query 2
——————-
select * from local_college_course l, ( <insert Query 1> ) k
where k.A=l.A and k.B=l.B and k.C=l.C
and then use THAT to join with grade_table:
update grade_table b
set b.D= ( select D from ( <insert QUERY 2> ) where trim ( yadda yadda yadda ….
You get the idea.
Good luck.
References :
test@ORA10G>
test@ORA10G> select a, b, c, d from t1;
A B C D
———- ———- ———- –
ALGEBRA 1234 9999
ALGEBRA 1234 9999
ALGEBRA 1234 9999
ALGEBRA 1234 9996
ALGEBRA 1234 9199
ALGEBRA 1234 9299
ALGEBRA 1234 9013
ALGEBRA 1234 9988
8 rows selected.
test@ORA10G> select a, b, c, d from t2;
A B C D
———- ———- ———- –
ALGEBRA 1234 9999 X7
ALGEBRA 1234 9999 A2
ALGEBRA 1234 9999 P6
ALGEBRA 1234 9996 F8
ALGEBRA 1234 9199 K1
ALGEBRA 1234 9299 E2
ALGEBRA 1234 9013 C9
ALGEBRA 1234 9988 Z5
8 rows selected.
test@ORA10G>
test@ORA10G> — update table t1 now
test@ORA10G> –
test@ORA10G> UPDATE t1
2 SET d =
3 (SELECT new_d
4 FROM (SELECT DISTINCT t1.a, t1.b, t1.c, t1.d,
5 CASE
6 WHEN COUNT (*)
7 OVER (PARTITION BY t1.a, t1.b, t1.c)=1
8 THEN t2.d
9 ELSE NULL
10 END AS new_d
11 FROM t1, t2
12 WHERE t1.a = t2.a
13 AND t1.b = t2.b
14 AND t1.c = t2.c) t
15 WHERE t.a = t1.a AND t.b = t1.b AND t.c = t1.c)
16 WHERE EXISTS (
17 SELECT NULL
18 FROM (SELECT DISTINCT t1.a, t1.b, t1.c, t1.d,
19 CASE
20 WHEN COUNT (*)
21 OVER (PARTITION BY t1.a, t1.b, t1.c) = 1
22 THEN t2.d
23 ELSE NULL
24 END AS new_d
25 FROM t1, t2
26 WHERE t1.a = t2.a
27 AND t1.b = t2.b
28 AND t1.c = t2.c) t
29 WHERE t.a = t1.a AND t.b = t1.b AND t.c = t1.c);
8 rows updated.
test@ORA10G>
test@ORA10G> — check the data in t1
test@ORA10G> select a, b, c, d from t1;
A B C D
———- ———- ———- –
ALGEBRA 1234 9999
ALGEBRA 1234 9999
ALGEBRA 1234 9999
ALGEBRA 1234 9996 F8
ALGEBRA 1234 9199 K1
ALGEBRA 1234 9299 E2
ALGEBRA 1234 9013 C9
ALGEBRA 1234 9988 Z5
8 rows selected.
test@ORA10G>
test@ORA10G>
HTH,
techieguy
References :
Try this
SELECT a, b, c, CASE d_count
WHEN 1 THEN d
ELSE ‘ ‘
END
FROM
(SELECT a, b, c, max(d) as d, count(*) as d_count FROM 4key GROUP BY a, b, c)
References :