Re: [sqlite] add new column to table
Hi Dennis, I tried something similar to your recommendation, but there was a problem with the following INSERT INTO call_data SELECT old_call_data.*, firstname || ' ' || surname FROM old_call_data JOIN users USING extn_no; there is a syntax error near extn_no. Lloyd - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: Sent: Wednesday, January 12, 2005 5:21 PM Subject: Re: [sqlite] add new column to table 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" 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_nameVARCHAR(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
Re: [sqlite] add new column to table
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" 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_nameVARCHAR(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
Re: [sqlite] add new column to table
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" 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 can't use an UPDATE statement to fill in your new column unless you want all rows to have the same value. You need to populate your new table using the INSERT ... SELECT ... statement. You use the select statement to combine the data from your old table and your new values using joins to pull values from other tables or calculating the new values based on existing columns. I hope this helps. Dennis Cote
Re: [sqlite] add new column to table
EDIT The only working example I can see is the following. BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; CREATE TABLE t1(a,b); INSERT INTO t1 SELECT a,b FROM t1_backup; DROP TABLE t1_backup; COMMIT; I can not find any help or FAQ regarding UPDATES with SUB SELECTS. What is the URL? Lloyd - Original Message - From: "Bert Verhees" <[EMAIL PROTECTED]> To: Sent: Tuesday, January 11, 2005 7:55 AM Subject: Re: [sqlite] add new column to table Op maandag 10 januari 2005 23:56, schreef Lloyd Thomas: 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? The FAQ gives a working example, I used it a lot, please try that. Bert Verhees - Original Message - From: "Lloyd Thomas" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 9:08 PM Subject: Re: [sqlite] add new column to table > Thanks Paul, >I have used that example before to recreate a table. > Can I > use the same thing to recreate a table and populate a new column with > data from a select query. > The table I wish to recreate has an ID number in it and I wish to use > this to get the forename and surname from another table. > > Lloyd > - Original Message - > From: "Paul Dixon" <[EMAIL PROTECTED]> > To: > Sent: Monday, January 10, 2005 5:50 PM > Subject: Re: [sqlite] add new column to table > >> Lloyd Thomas wrote: >>>I wish to create a new column in a table and add data, which is >>>queried >>>from another table.What is the best way? >> >> There's no "ALTER TABLE" support in the support language, so you have >> to >> recreate the entire table and re-populate it. >> >> Check the FAQ: http://www.sqlite.org/faq.html#q13 >> >> Paul -- Met vriendelijke groet Bert Verhees ROSA Software
Re: [sqlite] add new column to table
The only working example I can see is the following. BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; CREATE TABLE t1(a,b); INSERT INTO t1 SELECT a,b FROM t1_backup; DROP TABLE t1_backup; COMMIT;I can see any help or FAQ regarding UPDATES with SUB SELECTS. What is the URL?Lloyd- Original Message - From: "Bert Verhees" <[EMAIL PROTECTED]> To: Sent: Tuesday, January 11, 2005 7:55 AM Subject: Re: [sqlite] add new column to table Op maandag 10 januari 2005 23:56, schreef Lloyd Thomas: 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? The FAQ gives a working example, I used it a lot, please try that. Bert Verhees - Original Message - From: "Lloyd Thomas" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 9:08 PM Subject: Re: [sqlite] add new column to table > Thanks Paul, >I have used that example before to recreate a table. Can > I > use the same thing to recreate a table and populate a new column with > data from a select query. > The table I wish to recreate has an ID number in it and I wish to use > this to get the forename and surname from another table. > > Lloyd > - Original Message - > From: "Paul Dixon" <[EMAIL PROTECTED]> > To: > Sent: Monday, January 10, 2005 5:50 PM > Subject: Re: [sqlite] add new column to table > >> Lloyd Thomas wrote: >>>I wish to create a new column in a table and add data, which is >>>queried >>>from another table.What is the best way? >> >> There's no "ALTER TABLE" support in the support language, so you have >> to >> recreate the entire table and re-populate it. >> >> Check the FAQ: http://www.sqlite.org/faq.html#q13 >> >> Paul -- Met vriendelijke groet Bert Verhees ROSA Software
Re: [sqlite] add new column to table
Op maandag 10 januari 2005 23:56, schreef Lloyd Thomas: > 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? The FAQ gives a working example, I used it a lot, please try that. Bert Verhees > > > > - Original Message - > From: "Lloyd Thomas" <[EMAIL PROTECTED]> > To: > Sent: Monday, January 10, 2005 9:08 PM > Subject: Re: [sqlite] add new column to table > > > Thanks Paul, > >I have used that example before to recreate a table. Can I > > use the same thing to recreate a table and populate a new column with > > data from a select query. > > The table I wish to recreate has an ID number in it and I wish to use > > this to get the forename and surname from another table. > > > > Lloyd > > - Original Message ----- > > From: "Paul Dixon" <[EMAIL PROTECTED]> > > To: > > Sent: Monday, January 10, 2005 5:50 PM > > Subject: Re: [sqlite] add new column to table > > > >> Lloyd Thomas wrote: > >>>I wish to create a new column in a table and add data, which is queried > >>>from another table.What is the best way? > >> > >> There's no "ALTER TABLE" support in the support language, so you have to > >> recreate the entire table and re-populate it. > >> > >> Check the FAQ: http://www.sqlite.org/faq.html#q13 > >> > >> Paul -- Met vriendelijke groet Bert Verhees ROSA Software
Re: [sqlite] add new column to table
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? - Original Message - From: "Lloyd Thomas" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 9:08 PM Subject: Re: [sqlite] add new column to table Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: "Paul Dixon" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 5:50 PM Subject: Re: [sqlite] add new column to table Lloyd Thomas wrote: I wish to create a new column in a table and add data, which is queried from another table.What is the best way? There's no "ALTER TABLE" support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
Re: [sqlite] add new column to table
Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: "Paul Dixon" <[EMAIL PROTECTED]> To: Sent: Monday, January 10, 2005 5:50 PM Subject: Re: [sqlite] add new column to table Lloyd Thomas wrote: I wish to create a new column in a table and add data, which is queried from another table.What is the best way? There's no "ALTER TABLE" support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
Re: [sqlite] add new column to table
Lloyd Thomas wrote: I wish to create a new column in a table and add data, which is queried from another table.What is the best way? There's no "ALTER TABLE" support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
