Re: [sqlite] add new column to table

[Dennis Cote]

Lloyd Thomas wrote:
Thanks Dennis.
   As long as I know where I stand. I can probably use PHP or Delphi
to update each row manually.
Lloyd
----- Original Message -----
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: "sqlite-users" <sqlite-users@sqlite.org>
Sent: Wednesday, January 12, 2005 4:21 PM
Subject: Fw: [sqlite] add new column to table

Dennis Cote wrote:
Lloyd Thomas wrote:
Thanks. That is going OK but I am having a problem with updating
the new column with the info I need. It seems to update with the
same entry from my users table to all rows.
UPDATE call_data SET caller_name = (SELECT firstname || surname AS
'caller_name' FROM users WHERE extn_no = (SELECT extn_no FROM
call_data));
I have missed something?
Lloyd,
You shouldn't need to use PHP or Delphi.
You need to create a tempoarary copy of your existing table, then delete and 
recreate your table with the new column added. Then use the insert command 
to copy the old data nad the new data (from your users table) back into the 
new table. Finally you can delete the old copy.

You need to do something like the following:
CREATE TABLE old_call_data AS
   SELECT * FROM call_data;
DROP TABLE call_data;
CREATE TABLE call_data (
   .....,
   caller_name    VARCHAR(100)
   );
INSERT INTO call_data
   SELECT old_call_data.*, firstname || ' ' || surname
   FROM old_call_data JOIN users USING extn_no;
DROP TABLE old_call_data;
I have assumed that you will add the new column at the end of the existing 
column list. If not your select statement simply gets a little more 
compilcated.

HTH
Dennis Cote