Thread: Inserting data of two other tables
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I need to insert data into a table getting it from two other tables. I've tried to do it wiht an INSERT INTO statement, but I don't know exactly why it seems it doesn't work. At least the operation gets "hanged" for plenty of time until I cancel it :-( The sentence that I try to make what I want is: INSERT INTO totaltrajectes (turno, condu, linea, fecha, nserie, nseriechip,tipotrbus,paraor,parade,hora1hora,hora1min,pasaindi,numvez,clase, tarifa,tarneto,tarbruto,formapago,texto,journey,salreca) SELECT turno,condu,linea,fecha,nserie,nseriechip,tipotrbus,paraor,parade, hora1hora,hora1min,pasaindi,numvez,clase,tarifa,tarneto,tarbruto, formapago,texto,journey,salreca from captrajectes,detalltrajectes Also I have to mention that this sentence works with about 650.000 rows ~ in the table "detalltrajectes" and 35.000 in the "captrajectes" one. The fields turno, condu, linea and fecha are from captrajectes, and the rest from detalltrajectes. Captrajectes is a header file and every row from this table refers to many rows of detalltrajectes, and they get linked with two fields that are identical. In captrajectes pkey is the primary key and in detalltrajectes journey is the field that refers to pkey letting us know to wich header every row belongs. Does anybody know a more effective way to fill the new table? At least to make me able to do it... Thanks in advance for your help. Josep Sànchez ~ [papapep] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+zmxK2vx52x0kyz4RAjl/AKDBFZTC08EQmytShbRyExe9+hfqWgCeLPtu oAn2OmnicvXXEZH6eLfv/84= =MuaD -----END PGP SIGNATURE-----
On Fri, 23 May 2003 20:45:32 +0200, papapep <papapep@gmx.net> wrote:
>INSERT INTO totaltrajectes ([...])
>SELECT [...]
>FROM captrajectes,detalltrajectes
>
>Also I have to mention that this sentence works with about 650.000 rows
>~ in the table "detalltrajectes" and
>35.000 in the "captrajectes" one.
>Does anybody know a more effective way to fill the new table?
Yes. Use a WHERE clause :-) Your join produces 650000 * 35000 rows.
This should work:
... FROM captrajectes AS c, detalltrajectes AS d
WHERE c.pkey = d.journey;
Servus
Manfred
papapep <papapep@gmx.net> writes:
> INSERT INTO totaltrajectes (turno, condu, linea, fecha, nserie,
> nseriechip,tipotrbus,paraor,parade,hora1hora,hora1min,pasaindi,numvez,clase,
> tarifa,tarneto,tarbruto,formapago,texto,journey,salreca) SELECT
> turno,condu,linea,fecha,nserie,nseriechip,tipotrbus,paraor,parade,
> hora1hora,hora1min,pasaindi,numvez,clase,tarifa,tarneto,tarbruto,
> formapago,texto,journey,salreca from captrajectes,detalltrajectes
er ... no WHERE clause?
> Also I have to mention that this sentence works with about 650.000 rows
> ~ in the table "detalltrajectes" and
> 35.000 in the "captrajectes" one.
With no WHERE clause, your SELECT is forming the unconstrained cross
product, with upwards of 22 billion rows. No wonder it takes awhile
(it'd likely run you out of disk space, too).
> Captrajectes is a header file and every row from this table refers to
> many rows of detalltrajectes, and they get linked with two fields that
> are identical. In captrajectes pkey is the primary key and in
> detalltrajectes journey is the field that refers to pkey letting us know
> to wich header every row belongs.
If that's the behavior you want, you need to say so with a WHERE clause.
Or JOIN ... ON ....
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 | er ... no WHERE clause? ....yes.....really....well.... [:-| Thanks to Tom and Manfred for their "suggestion". Now it works fine, and I've made the operation in less than 10 minutes. Thanks again. Josep Sanchez ~ [papapep] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+zz2t2vx52x0kyz4RAubVAJ9k/tLrJxyP4KkWYF5d2O1dQ8xKegCfdnAw /BKo6cGregHXsO+NJMDSXRM= =xOos -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Continuing with my two-tables problem, and knowing that DELETE only works with one table, how do I remove rows from, for example, a certain day, considering that in the second table I haven't got that field, and that it is in the "header" table (captrajectes)? I've read that I have to create a transaction, but I can't really see how to do it. Can you give me a hand? Thanks in advance. Josep Sànchez ~ [papapep] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+0lEM2vx52x0kyz4RAhBKAJ9Q1aCOu/Vf4VpyLzbsLb+3cJnZUwCgtRYO VRBEgtpyQccvviVhbiHDwuk= =WYHr -----END PGP SIGNATURE-----
Am Mon, 2003-05-26 um 19.38 schrieb papapep: DELETE ... FROM ... WHERE e.g.: DELETE FROM a WHERE datum='17.07.1999'::date AND NOT EXISTS (SELECT NULL FROM b WHERE b.field=a.field); or something like that HTH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Nabil Sayegh wrote:
| DELETE FROM a WHERE datum='17.07.1999'::date
~ ^^^^^^^^^^^^
This data is not in the "a" table, is in the b one. And the records I
need to remove are the ones from the b table. Will it work this way?
| AND NOT EXISTS (SELECT NULL FROM b WHERE b.field=a.field);
What does this part do? It selects rows where b.field and a.field are
not the same? And the "AND NOT EXISTS"? It inverts the query? I mean, if
the intraparenthesys part selects the rows where b.field <> a.field,
after you change the "sign", selecting the rows that DO have the
b.field=a.field?
I'm a bit confused...
Josep Sànchez
~ [papapep]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+00Lh2vx52x0kyz4RArDQAKDODxurcQ8vAcm2Yqpwy++JoAKkfQCgrlH8
ffb9xgmRvQd2HH/0ycT9u8M=
=D900
-----END PGP SIGNATURE-----
Am Die, 2003-05-27 um 12.50 schrieb papapep:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Nabil Sayegh wrote:
>
> | DELETE FROM a WHERE datum='17.07.1999'::date
> ~ ^^^^^^^^^^^^
> This data is not in the "a" table, is in the b one. And the records I
> need to remove are the ones from the b table. Will it work this way?
Then you have to do the opposite:
DELETE FROM b WHERE datum='17.07.1999'::date AND NOT EXISTS (SELECT NULL
FROM a WHERE a.field=b.field)
This query DELETEs rows from b where b.datum is a given date BUT ONLY
these rows where NO field in a EXISTS that refers to the rows in b.
The subquery together with NOT EXISTS returns bool. As we don't need to
return any info from a I just select NULL.
(I didn't test it, but you should get the idea behind it)
>
> | AND NOT EXISTS (SELECT NULL FROM b WHERE b.field=a.field);
>
> What does this part do? It selects rows where b.field and a.field are
> not the same? And the "AND NOT EXISTS"? It inverts the query? I mean, if
No, it selects rows where b.field and a.field _ARE_ the same.
This is just a JOIN. Usually you do this with foreign keys.
b
--------------------------
id_person name datum
1 John 10.03.1995
2 Peter 12.03.1997
3 Alice 17.07.1999
id_person is a PRIMARY KEY here
a
--------------------------
id_person article price
1 scanner 500
1 monitor 300
2 Camera 1000
id_person is a FOREIGN KEY here
DELETE FROM b WHERE datum='17.07.1999'::date AND NOT EXISTS (SELECT NULL
FROM a WHERE a.id_person=b.id_person)
This would delete Alice from b because she hasn't a row in a.
> the intraparenthesys part selects the rows where b.field <> a.field,
> after you change the "sign", selecting the rows that DO have the
> b.field=a.field?
>
> I'm a bit confused...
You should read about FOREIGN KEYs
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I've made a try with this: SELECT count(journey) from DETALLTRAJECTES where EXISTS (SELECT NULL from CAPTRAJECTE,DETALLTRAJECTES where pkey=journey and fecha=20030423); and it doesn't work properly (at least it doesn't do what I need...) In my last mail (this is for Nabil) I made a mistake in what I told you. The date to filter the rows to remove is in CAPTRAJECTES, and the rows to remove are in DETALLTRAJECTES. CAPTRAJECTES has a field called "pkey" and DETALLTRAJECTES another called "journey" that are the join between the two tables. When I execute the select above noted it returns me about 15.000 rows. But if I make this one: SELECT count(journey) from CAPTRAJECTES,DETALLTRAJECTES where pkey=journey and fecha=20030423; it only returns me about 5.000. So the first one has any error in the conception. Where do I make the mistake? (thanks you all for your patience.....) Josep Sànchez ~ [papapep] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+1LEd2vx52x0kyz4RAqk0AKDOo3ZoYTYKC5Pcfgov90+CLydxCwCfVgXM RF+NLu0hs6iyR+QKGkjCbzk= =wOrJ -----END PGP SIGNATURE-----
Am Mit, 2003-05-28 um 14.52 schrieb papapep: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > I've made a try with this: > > SELECT count(journey) from DETALLTRAJECTES where EXISTS (SELECT NULL > from CAPTRAJECTE,DETALLTRAJECTES where pkey=journey and fecha=20030423); > > and it doesn't work properly (at least it doesn't do what I need...) > > In my last mail (this is for Nabil) I made a mistake in what I told you. > The date to filter the rows to remove is in CAPTRAJECTES, and the rows > to remove are in DETALLTRAJECTES. CAPTRAJECTES has a field called "pkey" > and DETALLTRAJECTES another called "journey" that are the join between > the two tables. please send us the output of all involved tables with \d e.g.: \d DETALLTRAJECTES bye -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Nabil Sayegh wrote: | please send us the output of all involved tables with \d Here they are: | Table "captrajectes" | Column | Type | Modifiers | ------------+---------------+----------- | traye | numeric(5,0) | | linea | numeric(4,0) | | filler | character(4) | | n-bill-in | numeric(9,0) | | n-bill-fi | numeric(9,0) | | n-bono-in | numeric(9,0) | | n-bono-fi | numeric(9,0) | | c-bono-in | numeric(9,0) | | c-bono-fi | numeric(9,0) | | turno | numeric(4,0) | | condu | numeric(4,0) | | horahora | numeric(2,0) | | horamin | numeric(2,0) | | horafihora | numeric(2,0) | | horafimin | numeric(2,0) | | fecha | numeric(8,0) | not null | maquina | numeric(6,0) | | bus | numeric(4,0) | | billenet | numeric(5,0) | | euronet | numeric(7,2) | | eurovalor | numeric(7,2) | | pkey | character(10) | not null | trec | numeric(6,0) | | Primary key: pkey_fecha | | Table "detalltrajectes" | Column | Type | Modifiers | ------------+---------------+----------- | nserie | numeric(9,0) | | nseriechip | numeric(10,0) | | tipotrbus | numeric(2,0) | | paraor | numeric(4,0) | | parade | numeric(4,0) | | hora1hora | numeric(2,0) | | hora1min | numeric(2,0) | | pasaindi | numeric(2,0) | | numvez | character(2) | | clase | numeric(4,0) | | tarifa | numeric(4,0) | | tarneto | numeric(8,2) | | tarbruto | numeric(8,2) | | formapago | numeric(1,0) | | texto | character(1) | | journey | character(10) | | salreca | numeric(9,2) | Josep Sànchez ~ [papapep] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+1L7R2vx52x0kyz4RArzkAKCG5pYRrHRQ/MCTekngl5R97oWCpQCfblzv L/yMj/OHn00+unppRMFAggk= =Cqjl -----END PGP SIGNATURE-----