SQL: Joining two tables in UPDATE command

[sonxEr77]

Hi, have two tables ORG & ORG_EXT .. they share data on column trade_number. But, trade_number on ORG is smaller case whilst on ORG_EXT is upper case.. I tried the following scripts but getting errors

update ORGANISATION001 o, ORGANISATION_EXTENSION001 oe SET o.trade_number=upper(o.trade_number) where o.trade_number = oe.trade_number; 
update o SET o.trade_number=upper(o.trade_number) from ORGANISATION001 o inner join ORGANISATION_EXTENSION001 oe on o.trade_number = oe.trade_number;

Any suggestions?

 

[Ancalagon]

you need something like:

update o
set field = value,
field2 = value2
from table1 o
join table 2 o2 on o.pk = o2.fk
where condition is true

 

[sonxEr77]

you need something like:

update o
set field = value,
field2 = value2
from table1 o
join table 2 o2 on o.pk = o2.fk
where condition is true

Please look at my second script and tell me whats missing ... is it "where condition is true"?

 

[Ancalagon]

You're setting trade_number to itself because the right hand part of your assignment statement uses the o table and not oe.

 

[Moederloos]

simply:

update table1 inner join table 2 on table1.field = table2.field set table2.otherfield = something
where conditions;

not all dbs accept update on joins though.

 

[sonxEr77]

simply:

update table1 inner join table 2 on table1.field = table2.field set table2.otherfield = something
where conditions;

not all dbs accept update on joins though.

Maybe i did not make myself clear, am only changing lowercase of table1 column 'trade_number' to uppercase. The reason for the Join is those trade numbers must also appear on table2. But am not changing anything on table 2

 

[Moederloos]

Maybe i did not make myself clear, am only changing lowercase of table1 column 'trade_number' to uppercase. The reason for the Join is those trade numbers must also appear on table2. But am not changing anything on table 2

how many records?

update table1 set col = ucase (col) where keyfield in (select keyfield from table2);

will work with small row counts, if the fields are key fields.

 

[Raithlin]

Can't you use referential integrity to ensure that the trade numbers exist? That would be much easier...

 

[rorz0r]

First, what database are you using?

 

[sonxEr77]

how many records?

update table1 set col = ucase (col) where keyfield in (select keyfield from table2);

will work with small row counts, if the fields are key fields.

6 records ... That looks right ... But why would it only work for fewer records? and trade_number is not primary key if that what you meant

 

[sonxEr77]

First, what database are you using?

Oracle 10g

 

[Moederloos]

6 records ... That looks right ... But why would it only work for fewer records? and trade_number is not primary key if that what you meant

It will work for a lot of records - but it gets slow. With a million or more records, prepare to wait a long time...
But it seemed from the context that this was a one off type thing, so sometimes it works better to do it the "long" way.