A respondent sent me the solution to my problem. Instead of using
UPDATE to modify the contents of a column, the solution uses REPLACE
and UNIQUE constraints on join keys:
create table A(i unique, x);
create table B(i unique, y);
insert into A('i', 'x') values (1, 0.1);
insert into A('i', 'x') values (2, 0.2);
insert into A('i', 'x') values (3, 0.3);
insert into B('i', 'y') values (1, -11.1);
insert into B('i', 'y') values (2, -22.2);
insert into B('i', 'y') values (3, -33.3);
replace into B(i,y) select A.i,x from A inner join B on B.i=A.i;
-- Al
On Thu, 13 May 2004 18:58:17 -0700, Al Danial <[EMAIL PROTECTED]> wrote:
>
> How does one copy the contents of a column from one
> table into another table (not altering the table, just
> overwriting an existing column in the second table)?
>
> For example say I have two tables A and B and I want to
> overwrite the contents of B.y with A.x:
>
> create table A(i, x);
> create table B(i, y);
>
> insert into A('i', 'x') values (1, 0.1);
> insert into A('i', 'x') values (2, 0.2);
> insert into A('i', 'x') values (3, 0.3);
>
> insert into B('i', 'y') values (1, -11.1);
> insert into B('i', 'y') values (2, -22.2);
> insert into B('i', 'y') values (3, -33.3);
>
> I tried the obvious with:
>
> update B set y=(select x from A);
>
> but this ends up duplicating the contents of only
> the first row of A.x into B.y:
>
> select * from B;
>
> i y
> ---------- ----------
> 1 0.1
> 2 0.1
> 3 0.1
>
> If I work only with one table then the update works as expected:
>
> update B set y=i;
>
> select * from B;
>
> i y
> ---------- ----------
> 1 1
> 2 2
> 3 3
>
> Is there an undocumented limitation on sqlite's update?
> I'm using version 2.8.13. -- Al
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
