ok, getting closer... now what is the right syntax for the "---" part of the
delete?
C:\jon>cat t2.lis | gawk "/.*/ { print \"delete from \" $3 \" where \" $4 }"
|cut -d"," -f1|gawk "/.*/ {gsub(/VALUES\(/,\"--- \");print$0\";\"}"delete from
"parent01" where --- 1417556005;delete from "child01" where ---
1417626376;delete from "child01" where --- 1417626391;delete from "child01"
where --- 1417703626;delete from "child01" where --- 1417703753;delete from
"child01" where --- 1419259626;
From: Jonathan Leslie <[email protected]>
To: Jonathan Leslie <[email protected]>; General Discussion of SQLite
Database <[email protected]>
Sent: Tuesday, December 30, 2014 1:19 PM
Subject: Re: [sqlite] SQL newbie, how to implement a delete correctly.
C:\jon>cat t2.lis | gawk "/.*/ { print \"delete from \" $3 \" where \" $4 }"
|cut -d"," -f1delete from "parent01" where VALUES(1417556005delete from
"child01" where VALUES(1417626376delete from "child01" where
VALUES(1417626391delete from "child01" where VALUES(1417703626delete from
"child01" where VALUES(1417703753delete from "child01" where VALUES(1419259626
From: Jonathan Leslie <[email protected]>
To: Jonathan Leslie <[email protected]>; General Discussion of SQLite
Database <[email protected]>; General Discussion of SQLite Database
<[email protected]>
Sent: Tuesday, December 30, 2014 1:02 PM
Subject: Re: [sqlite] SQL newbie, how to implement a delete correctly.
Ok, I forgot to mention. I had the presence of mind to make sure that all
primary keys are unique for the entire database. In other words, if I have a
value for a primary key in one table, that number will never be used by any
other table in that database. Using the ".dump" facility and grep generated
something very interesting:
C:\jon>sqlite3 simple_test.db3 ".dump" >t.lis
C:\jon>grep 1417556005 t.lis >t2.lis
C:\jon>cat t2.lis
INSERT INTO "parent01" VALUES(1417556005,'second record
sgs_atm',33.0,44.0,0,999);INSERT INTO "child01" VALUES(1417626376,'first record
here!!!!','anotherfilename.txt',1417556005,1417561613,1417556069,1417626207,'myfile.txt');INSERT
INTO "child01" VALUES(1417626391,'second record, same as
first','anotherfilename.txt',1417556005,1417561613,1417556069,1417626207,'myfile.txt');INSERT
INTO "child01" VALUES(1417703626,'second record, same as first duplcated for
CSCI','anotherfilename.txt',1417556005,1417561613,1417556069,1417626207,'myfile.txt');INSERT
INTO "child01" VALUES(1417703753,'second record, same as first ok so I change
the description a
bit.','anotherfilename.txt',1417556005,1417561613,1417556069,1417626207,'myfile.txt');INSERT
INTO "child01" VALUES(1419259626,'second record, same as first duplcated for
CSCI','anotherfilename.txt',1417556005,1417561613,1417556069,1417710476,'myfile.txt');
so now I have a list of records that have the id 1417556005 in it.
From: Jonathan Leslie <[email protected]>
To: General Discussion of SQLite Database <[email protected]>
Sent: Tuesday, December 30, 2014 12:52 PM
Subject: Re: [sqlite] SQL newbie, how to implement a delete correctly.
thank you:
C:\jon>sqlite3 simple_test.db3 "select * from sqlite_master" >t3.lis
C:\jon>grep "table|" t3.listable|5DOF_ICs|5DOF_ICs|2|CREATE TABLE "5DOF_ICs"
(table|5dof_limits|5dof_limits|4|CREATE TABLE "5dof_limits"
(table|LMCS_ICs|LMCS_ICs|6|CREATE TABLE LMCS_ICs (table|ac_cfg|ac_cfg|8|CREATE
TABLE ac_cfg (table|camera_cfg|camera_cfg|9|CREATE TABLE camera_cfg
(table|ownship_motion_profile|ownship_motion_profile|10|CREATE TABLE
ownship_motion_profile
(table|ownship_motion_summary|ownship_motion_summary|12|CREATE TABLE
ownship_motion_summary (table|scenario_summary|scenario_summary|13|CREATE TABLE
scenario_summary (table|script_summary|script_summary|14|CREATE TABLE
script_summary (table|scripts|scripts|16|CREATE TABLE scripts
(table|test_matrix_table|test_matrix_table|17|CREATE TABLE test_matrix_table
(table|sgs_atm_cfg|sgs_atm_cfg|18|CREATE TABLE sgs_atm_cfg
(table|sgs_cfg|sgs_cfg|20|CREATE TABLE sgs_cfg
(table|sgs_mirage_cal_curve|sgs_mirage_cal_curve|22|CREATE TABLE
sgs_mirage_cal_curve (table|sgs_objects|sgs_objects|23|CREATE TABLE sgs_objects
(table|sgs_scene_graph|sgs_scene_graph|25|CREATE TABLE sgs_scene_graph
(table|sgs_scene_graph_nodes|sgs_scene_graph_nodes|26|CREATE TABLE
sgs_scene_graph_nodes (table|sgs_sensor_cfg|sgs_sensor_cfg|28|CREATE TABLE
sgs_sensor_cfg (table|sim_attenuation|sim_attenuation|29|CREATE TABLE
sim_attenuation
(table|sim_attenuation_summary|sim_attenuation_summary|31|CREATE TABLE
sim_attenuation_summary (table|st_launch_params|st_launch_params|32|CREATE
TABLE st_launch_params (table|st_scenario|st_scenario|33|CREATE TABLE
st_scenario (table|threat_cfg|threat_cfg|36|CREATE TABLE threat_cfg
(table|cm_cfg|cm_cfg|38|CREATE TABLE cm_cfg (
From: J Decker <[email protected]>
To: Jonathan Leslie <[email protected]>; General Discussion of SQLite
Database <[email protected]>
Sent: Tuesday, December 30, 2014 12:47 PM
Subject: Re: [sqlite] SQL newbie, how to implement a delete correctly.
On Tue, Dec 30, 2014 at 9:40 AM, Jonathan Leslie <[email protected]>
wrote:
> PRAGMA foreign_key_list(table-name)
> I don't know the table-name, how do I get a list of table names in the
> database?
>
> select * from sqlite_master
>
> From: Simon Slavin <[email protected]>
> To: Jonathan Leslie <[email protected]>; General Discussion of
> SQLite Database <[email protected]>
> Sent: Tuesday, December 30, 2014 11:14 AM
> Subject: Re: [sqlite] SQL newbie, how to implement a delete correctly.
>
>
> On 30 Dec 2014, at 3:53pm, Jonathan Leslie <[email protected]> wrote:
>
> > Sorry, I wasn't clear. what if there are other child tables, say
> child02- childxx, and you don't know the names of the table, you want to
> search the entire database?
>
> SQLite has no command which will help it specifically find all the child
> rows of a row. You will have to use a set of SELECT commands like the one
> Hick posted.
>
> It would be possible to write some clever code which came up with this
> list. You would want to start off executing
>
> PRAGMA foreign_key_list(table-name)
>
> and looking at the results you get back from it. From those you could
> generate SELECT statements which found the child rows.
>
> Simon.
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
