Re: UPDATE statement...
In article <6d78ue$ddl$1@news.igs.net>,
Pierluigi Buonicore <pluigib@tetranetsoftware.com> wrote:
>Hello there,
>I am running Informix OLDS 7.23 through ODBC from a C++ application.
>I tryed to execute the following SQL statement :
>
>UPDATE tableA, tableB
>SET tableA.UserName = tableB.NewName
>WHERE tableA.Name = tableB.OldName;
>
>but it doesn't work at all.
Let's see if I understand you a-right.
You have a table "tableB" like this:-
- tableB -
old new
------ ----
robert bob
david dave
and another table like this:-
- tableA -
first_name last_name
---------- -----------
david copperfield
robert frost
robert kennedy
and you want an update statement that will
change the data in TableA to look like this:-
first_name last_name
---------- -----------
dave copperfield
bob frost
bob kennedy
If that's what you want to do, then try this:
update tableA
set first_name = ( select tableB.new
from tableB
where tableB.old = tableA.first_name ) ;
- Paul (not a spokesman)
PS Here's the whole script that I tested with....
create temp table tableB ( old char(15), new char(15) ) ;
insert into tableB values ( 'robert' , 'bob' ) ;
insert into tableB values ( 'david' , 'dave' ) ;
select * from tableB ;
create temp table tableA ( first_name char(15), last_name char(15) ) ;
insert into tableA values ( 'robert' , 'frost' ) ;
insert into tableA values ( 'robert' , 'kennedy' ) ;
insert into tableA values ( 'david' , 'copperfield' ) ;
select * from tableA order by last_name ;
update tableA
set first_name = ( select tableB.new
from tableB
where tableB.old = tableA.first_name ) ;
select * from tableA order by last_name ;~