RE: [sqlite] Is this possible in SQLite?

[John O'Neill]

Hi Dennis,

Thanks for the reply.  In the original "INSERT" commands, my intention was to 
update a field in the columns as they were being copied to the new table.  
Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd 
like to select those items and update their primary keys to a new value (hence 
the "SET" command) as they are being inserted into the new table.

So for example, in table 'a' there might be a column that has primary key = 1, 
and in the copied version, I want to set that primary key = 2 or some other 
unique value.

I guess this question is can I combine an UPDATE...SET with an INSERT...SELECT 
command?

Thanks,
John

-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 17, 2005 4:32 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is this possible in SQLite?


John O'Neill wrote:

>Hello all,
>
>I have a fairly simple DB with two tables.  I'm trying to combine a 
>SELECT and UPDATE command, if it is possible:
>
>CREATE TABLE a (id PRIMARY KEY, data INT);
>CREATE TABLE b (id INT, data INT);
>
>INSERT INTO a VALUES( 1, 100 );
>INSERT INTO b VALUES( 1, 101 );
>INSERT INTO b VALUES( 1, 102 );
>INSERT INTO b VALUES( 1, 103 );
>...
>
>And at some point in the future, two new tables (possibly in a 
>different database) are created:
>
>CREATE TABLE acopy (id PRIMARY KEY, data INT);
>CREATE TABLE bcopy (id INT, data INT );
>
>Is there a way to do the following:
>
>INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X 
>); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X );
>
>Instead of doing the following 4 commands or SELECTing a and b into 
>TEMP tables:
>
>UPDATE a SET id = X WHERE id = 1;
>UPDATE b SET id = X WHERE id = 1;
>INSERT INTO acopy SELECT * FROM a;
>INSERT INTO bcopy SELECT * FROM b;
>
>Thanks,
>John
>
>
>
>  
>
John,

The following will do what you have asked (I substituted the letter 'X' 
for your new value X for clarity), but I'm not sure if this is general 
enough for your real needs.

CREATE TABLE a (id PRIMARY KEY, data INT);
CREATE TABLE b (id INT, data INT);

INSERT INTO a VALUES( 1, 100 );
INSERT INTO b VALUES( 1, 101 );
INSERT INTO b VALUES( 1, 102 );
INSERT INTO b VALUES( 1, 103 );

CREATE TABLE acopy (id PRIMARY KEY, data INT);
CREATE TABLE bcopy (id INT, data INT );

INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1;
INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1;

SELECT * FROM acopy;
SELECT * from bcopy;

HTH
Dennis Cote