Re: [sqlite] replacing a table
Sam Carleton wrote: > In other databases there have been times when I have played some tricks with > the master tables, an example is: > > Goal: change some fundamental characteristics of 'target_table' which cannot > be done by an ALTER > > 1: Create the new table with a different name: target_table2 with the > changes > 2: Do an insert/select to insert the contents of target_table into > target_table2 > 3: Drop target_table > 4: Modify the master table to change the name of target_table2 to > target_table > > According to the FAQ, sqlite_master is read-only. Is there any to pull of > this trick? Steps 1-3 just work. Step 4 can be done this way: alter table target_table2 rename to target_table; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replacing a table
On Sun, Jun 27, 2010 at 07:41:48PM -0400, Sam Carleton scratched on the wall: > 1: Create the new table with a different name: target_table2 with the changes > 2: Do an insert/select to insert the contents of target_table into > target_table2 > 3: Drop target_table > 4: Modify the master table to change the name of target_table2 to > target_table > > According to the FAQ, sqlite_master is read-only. Is there any to pull of > this trick? You can do this with "PRAGMA writeable_schema", but I think the real answer you're looking for is ALTER TABLE ... RENAME. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] replacing a table
In other databases there have been times when I have played some tricks with the master tables, an example is: Goal: change some fundamental characteristics of 'target_table' which cannot be done by an ALTER 1: Create the new table with a different name: target_table2 with the changes 2: Do an insert/select to insert the contents of target_table into target_table2 3: Drop target_table 4: Modify the master table to change the name of target_table2 to target_table According to the FAQ, sqlite_master is read-only. Is there any to pull of this trick? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
On Thu, Oct 15, 2009 at 10:37 AM, Kavita Raghunathan wrote: > Thanks much Pavel. > > No you are right I'm more of a L3 protocols > person with some user interfaces and network security, > somehow ended up trying to use sqlite now. I'll look through > your link on SQL. My advice -- since you are now working with a SQL database, you would become way more efficient if you followed Pavel's advice and learned SQL basics. The basics are really not that hard, but your questions display that you are missing that knowledge. Once you know the basics, you will be well on your way, and will really be encountering SQL-specific and sqlite-specific hurdles. That is where this list comes in to help and becomes useful. > > Regards, > Kavita > - Original Message - > From: "Pavel Ivanov" > To: "General Discussion of SQLite Database" > Cc: "Kelvin Xu" > Sent: Thursday, October 15, 2009 10:28:23 AM GMT -06:00 US/Canada Central > Subject: Re: [sqlite] Replacing a table > > I guess you didn't work with SQL anywhere in your developer life, > right? Read some books or internet pages about it. You can start from > here http://en.wikipedia.org/wiki/SQL and follow any links there. > > To update column in all rows of the table you need to issue the > following statement: > > UPDATE table_name SET column_name = value > > It doesn't require you to loop through all rows although DBMS will > iterate all of them for you. > > > Pavel > > On Thu, Oct 15, 2009 at 11:22 AM, Kavita Raghunathan > wrote: >> Thanks Pavel and Owen. This is very useful information. >> >> Also how can we change a whole column at a time ? >> In otherwords, the entire column needing to be changed would >> involve looping through each entry and changing that value, >> instead i want to substitute a whole column. >> >> Thanks! >> Kavita >> - Original Message - >> From: "Owen O'Neill" >> To: "General Discussion of SQLite Database" >> Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central >> Subject: Re: [sqlite] Replacing a table >> >> >> Run the sql >> 'delete from "tablename";' >> >> if the table definition is different (different column names or data >> types ) then you will need to drop the table and create a new one. >> 'drop table "tablename";' >> >> http://www.sqlite.org/lang_createtable.html >> >> if the table is huge you might get different performance depending on >> whether your journal settings are to truncate or delete or pad etc. >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan >> Sent: Thursday, October 15, 2009 3:51 PM >> To: General Discussion of SQLite Database >> Cc: Kelvin Xu >> Subject: [sqlite] Replacing a table >> >> Hi, >> >> Is there a way to use the sqlite wrappers to "replace" or delete a table >> completely ? >> (without looping through and deleting each row and column) >> The number of columns and rows of the new table is identical to the >> number >> of columns and rows of the old table being replaced. Is there a quick >> way >> to do that? >> >> Thanks, >> Kavita >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
Pavel Ivanov wrote: > > > To update column in all rows of the table you need to issue the > following statement: > > UPDATE table_name SET column_name = value > And note that the "value" above does not have to be a constant. It can, for instance, depend on other values in the row being updated. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
Thanks much Pavel. No you are right I'm more of a L3 protocols person with some user interfaces and network security, somehow ended up trying to use sqlite now. I'll look through your link on SQL. Regards, Kavita - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Cc: "Kelvin Xu" Sent: Thursday, October 15, 2009 10:28:23 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Replacing a table I guess you didn't work with SQL anywhere in your developer life, right? Read some books or internet pages about it. You can start from here http://en.wikipedia.org/wiki/SQL and follow any links there. To update column in all rows of the table you need to issue the following statement: UPDATE table_name SET column_name = value It doesn't require you to loop through all rows although DBMS will iterate all of them for you. Pavel On Thu, Oct 15, 2009 at 11:22 AM, Kavita Raghunathan wrote: > Thanks Pavel and Owen. This is very useful information. > > Also how can we change a whole column at a time ? > In otherwords, the entire column needing to be changed would > involve looping through each entry and changing that value, > instead i want to substitute a whole column. > > Thanks! > Kavita > - Original Message - > From: "Owen O'Neill" > To: "General Discussion of SQLite Database" > Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central > Subject: Re: [sqlite] Replacing a table > > > Run the sql > 'delete from "tablename";' > > if the table definition is different (different column names or data > types ) then you will need to drop the table and create a new one. > 'drop table "tablename";' > > http://www.sqlite.org/lang_createtable.html > > if the table is huge you might get different performance depending on > whether your journal settings are to truncate or delete or pad etc. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan > Sent: Thursday, October 15, 2009 3:51 PM > To: General Discussion of SQLite Database > Cc: Kelvin Xu > Subject: [sqlite] Replacing a table > > Hi, > > Is there a way to use the sqlite wrappers to "replace" or delete a table > completely ? > (without looping through and deleting each row and column) > The number of columns and rows of the new table is identical to the > number > of columns and rows of the old table being replaced. Is there a quick > way > to do that? > > Thanks, > Kavita > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
I guess you didn't work with SQL anywhere in your developer life, right? Read some books or internet pages about it. You can start from here http://en.wikipedia.org/wiki/SQL and follow any links there. To update column in all rows of the table you need to issue the following statement: UPDATE table_name SET column_name = value It doesn't require you to loop through all rows although DBMS will iterate all of them for you. Pavel On Thu, Oct 15, 2009 at 11:22 AM, Kavita Raghunathan wrote: > Thanks Pavel and Owen. This is very useful information. > > Also how can we change a whole column at a time ? > In otherwords, the entire column needing to be changed would > involve looping through each entry and changing that value, > instead i want to substitute a whole column. > > Thanks! > Kavita > - Original Message - > From: "Owen O'Neill" > To: "General Discussion of SQLite Database" > Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central > Subject: Re: [sqlite] Replacing a table > > > Run the sql > 'delete from "tablename";' > > if the table definition is different (different column names or data > types ) then you will need to drop the table and create a new one. > 'drop table "tablename";' > > http://www.sqlite.org/lang_createtable.html > > if the table is huge you might get different performance depending on > whether your journal settings are to truncate or delete or pad etc. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan > Sent: Thursday, October 15, 2009 3:51 PM > To: General Discussion of SQLite Database > Cc: Kelvin Xu > Subject: [sqlite] Replacing a table > > Hi, > > Is there a way to use the sqlite wrappers to "replace" or delete a table > completely ? > (without looping through and deleting each row and column) > The number of columns and rows of the new table is identical to the > number > of columns and rows of the old table being replaced. Is there a quick > way > to do that? > > Thanks, > Kavita > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
No problem, Update "table" set "columnname"='newvalue'; Time to learn some sql basics and discover the 'where' clause :-) http://www.sqlite.org/lang_update.html -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Thursday, October 15, 2009 4:22 PM To: General Discussion of SQLite Database Cc: Kelvin Xu Subject: Re: [sqlite] Replacing a table Thanks Pavel and Owen. This is very useful information. Also how can we change a whole column at a time ? In otherwords, the entire column needing to be changed would involve looping through each entry and changing that value, instead i want to substitute a whole column. Thanks! Kavita - Original Message - From: "Owen O'Neill" To: "General Discussion of SQLite Database" Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Replacing a table Run the sql 'delete from "tablename";' if the table definition is different (different column names or data types ) then you will need to drop the table and create a new one. 'drop table "tablename";' http://www.sqlite.org/lang_createtable.html if the table is huge you might get different performance depending on whether your journal settings are to truncate or delete or pad etc. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Thursday, October 15, 2009 3:51 PM To: General Discussion of SQLite Database Cc: Kelvin Xu Subject: [sqlite] Replacing a table Hi, Is there a way to use the sqlite wrappers to "replace" or delete a table completely ? (without looping through and deleting each row and column) The number of columns and rows of the new table is identical to the number of columns and rows of the old table being replaced. Is there a quick way to do that? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
Thanks Pavel and Owen. This is very useful information. Also how can we change a whole column at a time ? In otherwords, the entire column needing to be changed would involve looping through each entry and changing that value, instead i want to substitute a whole column. Thanks! Kavita - Original Message - From: "Owen O'Neill" To: "General Discussion of SQLite Database" Sent: Thursday, October 15, 2009 9:59:32 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Replacing a table Run the sql 'delete from "tablename";' if the table definition is different (different column names or data types ) then you will need to drop the table and create a new one. 'drop table "tablename";' http://www.sqlite.org/lang_createtable.html if the table is huge you might get different performance depending on whether your journal settings are to truncate or delete or pad etc. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Thursday, October 15, 2009 3:51 PM To: General Discussion of SQLite Database Cc: Kelvin Xu Subject: [sqlite] Replacing a table Hi, Is there a way to use the sqlite wrappers to "replace" or delete a table completely ? (without looping through and deleting each row and column) The number of columns and rows of the new table is identical to the number of columns and rows of the old table being replaced. Is there a quick way to do that? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
Run the sql 'delete from "tablename";' if the table definition is different (different column names or data types ) then you will need to drop the table and create a new one. 'drop table "tablename";' http://www.sqlite.org/lang_createtable.html if the table is huge you might get different performance depending on whether your journal settings are to truncate or delete or pad etc. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan Sent: Thursday, October 15, 2009 3:51 PM To: General Discussion of SQLite Database Cc: Kelvin Xu Subject: [sqlite] Replacing a table Hi, Is there a way to use the sqlite wrappers to "replace" or delete a table completely ? (without looping through and deleting each row and column) The number of columns and rows of the new table is identical to the number of columns and rows of the old table being replaced. Is there a quick way to do that? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
> Is there a quick way to do that? DROP TABLE table1; ALTER TABLE table2 RENAME TO table1; And doesn't matter how many rows and columns have each of the tables. Hope I've understood your question correctly. Pavel On Thu, Oct 15, 2009 at 10:51 AM, Kavita Raghunathan wrote: > Hi, > > Is there a way to use the sqlite wrappers to "replace" or delete a table > completely ? > (without looping through and deleting each row and column) > The number of columns and rows of the new table is identical to the number > of columns and rows of the old table being replaced. Is there a quick way > to do that? > > Thanks, > Kavita > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Replacing a table
Hi, Is there a way to use the sqlite wrappers to "replace" or delete a table completely ? (without looping through and deleting each row and column) The number of columns and rows of the new table is identical to the number of columns and rows of the old table being replaced. Is there a quick way to do that? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users