Set t1.col2 = t2.col3 where t1.col1 = t2.col2 1

[tbarthen]

Simplified scenario:

Table t1
type_cd category_cd (need to populate this column)
abc
def
ghi
jkl
mno
pqr
stu
vwx


Table t2
tbl_id type_cd cat_cd
001 abc COL
002 def STA
003 ghi JDG
004 jkl SAC
005 mno MIN
006 pqr MKJ
007 stu DRG
008 vwx LIU

For each type_cd in Table t1 the category_cd shoud be set to the same value as t2.cat_cd where (t2.type_cd = t1.type_cd)

How do you write a valid update query for this scenario?

 

[Welshbird]

This tutorial will help:

http://www.techonthenet.com/sql/update.php

(In fact, that website is generally quite helpful to remind you of syntax and such-like)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[tbarthen]

Thanks for the valuable website. I'll read through it, and figure out what I can do.

To answer your statement:
"The question should be Is it worth trying to do? not Can it be done?" .......

Yes - it is worth trying to do, because the testing team I am supporting can not continue their testing at this point without getting those values populated
"Can it be done?" - I dunno. I hope so.

If there are any SQL masters out there who already know how to handle this update in an efficient manner, your suggestions are greatly appreciated. In the mean time, I'll see what I can figure out from the techonthenet website.

willif, thanks again for the useful link.

 

[SantaMufasa]

TBarthen,

For your tables with the following structures and contents:

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- -----------
 TYPE_CD                          VARCHAR2(3)
 CATEGORY_CD                      VARCHAR2(3)

SQL> desc t2
 Name                    Null?    Type
 ----------------------- -------- -----------
 TBL_ID                           VARCHAR2(3)
 TYPE_CD                          VARCHAR2(3)
 CAT_CD                           VARCHAR2(3)

select * from t1;

TYP CAT
--- ---
abc
def
ghi
jkl
mno
pqr
stu
vwx

8 rows selected.

select * from t2;

TBL TYP CAT
--- --- ---
001 abc COL
002 def STA
003 ghi JDG
004 jkl SAC
005 mno MIN
006 pqr MKJ
007 stu DRG
008 vwx LIU

8 rows selected.

...you can update the CATEGORY_CD column with this code:

update t1 set category_cd = 
       (select cat_cd from t2
         where t1.type_cd = t2.type_cd)
 where category_cd is null;

8 rows updated.

SQL> select * from t1;

TYP CAT
--- ---
abc COL
def STA
ghi JDG
jkl SAC
mno MIN
pqr MKJ
stu DRG
vwx LIU

8 rows selected.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tbarthen]

I get the following error using your update statement:
ORA-01427: single-row subquery returns more than one row

I think its my fault for simplifying the scenario too much.
Table t2 may actually have duplicates in the TYP column, and I believe this is what is causing the error.

In fact, there are duplicates of
select TYP, CAT from t2

Could I just use your query, but when there is more than 1 result say
select only the first result of:
(select cat_cd from t2
where t1.type_cd = t2.type_cd)

I understand that this approach could cause some cat_cd's to be skipped. That's alright with me.

 

[SantaMufasa]

If there are duplicates, then you can change the UPDATE to read:

update t1 set category_cd = 
       (select [b]max([/b]cat_cd[b])[/b] from t2
         where t1.type_cd = t2.type_cd)
 where category_cd is null;

Please confirm that this takes care of your problem.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tbarthen]

Can't believe I didn't think of that myself

Thanks Dave!

 

[Welshbird]

TBarthen,

Glad that Santa could offer you more help than I (I was just busy at the time I'm afraid).

(The question is my signature so wasn't directed at you!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]