Re: [sqlite] replacing a table

[Igor Tandetnik]

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

[Jay A. Kreibich]

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

Re: [sqlite] Replacing a table

[P Kishor]

On Thu, Oct 15, 2009 at 10:37 AM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> 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" <paiva...@gmail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Cc: "Kelvin Xu" <kelvin...@skyfiber.com>
> 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
> <kavita.raghunat...@skyfiber.com> 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" <oone...@averyberkel.com>
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> 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

[Gerry Snyder]

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

[Kavita Raghunathan]

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" <paiva...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Cc: "Kelvin Xu" <kelvin...@skyfiber.com>
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
<kavita.raghunat...@skyfiber.com> 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" <oone...@averyberkel.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> 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

[Pavel Ivanov]

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
<kavita.raghunat...@skyfiber.com> 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" <oone...@averyberkel.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> 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

[O'Neill, Owen]

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" <oone...@averyberkel.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
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

[Kavita Raghunathan]

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" <oone...@averyberkel.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
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

[O'Neill, Owen]

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

[Pavel Ivanov]

> 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