Hi Stephen,
I've attached a simple sql file that attempts to do as you are
suggestion. I've assumed some stuff about the table definitions. Let
me know if it helps. I tested on 10.4.2.0 but it should work for your
version.
-----------------------
Raymond Kroeker
On Sun, Feb 1, 2009 at 10:22, Stephen Dunton
<[email protected]> wrote:
> I'm losing my mind trying to figure out how to update one table from another
> in derby 10.3. Basically I want to do this:
>
>
>
> Update t1
>
> Set t1.col1 = t2.col2
>
> From t1, t2
>
> Where t1.pk = t2.fk
>
>
>
> I have tried every conceivable combination of aliasing / not aliasing,
> moving the from portion above set portion, you name it and keep getting an
> "Encountered "from"" syntax error.
>
>
>
> Is this type of statement supported in 10.3?
>
>
>
> I've seen stuff out there on the net and followed all of it but just can't
> get this type of update to work.
>
>
>
> Any help would be greatly appreciated
>
>
>
> Thanks,
>
>
>
> Stephen Dunton
>
> Software Developer
>
> GuideStar Technologies Inc.
>
> 303.640.6253
>
> [email protected]
>
>
--
---------------------------------------------------------
Raymond Kroeker
-- create t1
create table t1(id bigint generated always as identity(start with 1000),text varchar(32) not null,primary key(id),unique(text));
-- create t1
create table t2(t1_id bigint not null,id bigint generated always as identity(start with 2000),text varchar(32) not null,primary key(id),foreign key(t1_id) references t1(id),unique(text));
-- insert into t1
insert into t1(text) values('t1 data 0');
insert into t1(text) values('t1 data 1');
insert into t1(text) values('t1 data 2');
-- insert into t2
insert into t2(t1_id,text) values(1000,'t2 data 0');
insert into t2(t1_id,text) values(1000,'t2 data 1');
insert into t2(t1_id,text) values(1000,'t2 data 2');
-- select t1/t2
select * from t1;
select * from t2;
-- update t1
update t1 set text=(select t2.text from t2 where t2.id=2000) where id=1000;
-- select t1/t2
select * from t1;
select * from t2;
