[SQL] INSERT ... SELECT problem in Mysql
Hi! I was trying to port a solution from postgresql to mysql for a mysql user. im often using INSERT ... SELECTS with the same table in the INSERT and SELECT statments at the same time. In Postgres i used to use the destination only in a Subquery with IN, but in Mysql i had to join the table, but it didn´t work and threw this error : INSERT TABLE 'dummy' isn't allowed in FROM table list with this page explaining the error : http://www.mysql.de/doc/en/INSERT_SELECT.html I have a problem with this part in the text The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in standard SQL to SELECT from the same table into which you are inserting. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subquery clauses, the situation could easily be very confusing!) Is that correct? PostgreSQL allows this which is very handy, but breaks so some ANSI compliance. Can anyform that ANSI SQL doesn´t allow this? Thanx Christoph Nelles -- Mit freundlichen Grüssen Evil Azrael mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] INSERT ... SELECT problem in Mysql
Evil Azrael <[EMAIL PROTECTED]> writes: > I have a problem with this part in the text > > The target table of the INSERT statement cannot appear in the FROM > clause of the SELECT part of the query because it's forbidden in > standard SQL to SELECT from the same table into which you are > inserting. This is just MySQL's self-serving reading of the spec. What SQL92 actually says is (section 13.8) 1) The following restrictions apply for Intermediate SQL: a) The leaf generally underlying table of T shall not be gen- erally contained in the immediately contained in the except as the of a . SQL99 puts it a little differently: Conformance Rules 1) Without Feature F781, "Self-referencing operations", no leaf generally underlying table of T shall be generally contained in the immediately contained in the except as the or of a column reference. In other words, the behavior is perfectly standard, it's just not required for minimal SQL implementations to support it. What the standard behavior is is defined by this rule, earlier in the same section: 3) The is effectively evaluated before inserting any rows into B. that is, the SELECT doesn't see any rows that are being inserted into the target table during the same command. There would not be any need for that rule if selecting from the same table you're inserting into were flat-out forbidden, as the MySQL docs claim. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] INSERT ... SELECT problem in Mysql
Thanx for the fast answer. I already thought something about like this and considered sending the question to the advocacy list instead of the SQL list. I really like their docs since i´ve read about their reasons against Foreign Keys *g* One more point in favor for PostgreSQL :) Christoph Nelles Am Freitag, 4. Juli 2003 um 23:45 schrieben Sie: TL> Evil Azrael <[EMAIL PROTECTED]> writes: >> I have a problem with this part in the text >> >> The target table of the INSERT statement cannot appear in the FROM >> clause of the SELECT part of the query because it's forbidden in >> standard SQL to SELECT from the same table into which you are >> inserting. TL> This is just MySQL's self-serving reading of the spec. What SQL92 TL> actually says is (section 13.8) TL> 1) The following restrictions apply for Intermediate SQL: TL> a) The leaf generally underlying table of T shall not be gen- TL> erally contained in the immediately TL> contained in the except as the TL>of a . TL> SQL99 puts it a little differently: TL> Conformance Rules TL> 1) Without Feature F781, "Self-referencing operations", no leaf TL> generally underlying table of T shall be generally contained in TL> the immediately contained in the columns and source> except as the or TL> of a column reference. TL> In other words, the behavior is perfectly standard, it's just not TL> required for minimal SQL implementations to support it. TL> What the standard behavior is is defined by this rule, earlier in the TL> same section: TL> 3) The is effectively evaluated before inserting TL> any rows into B. TL> that is, the SELECT doesn't see any rows that are being inserted into TL> the target table during the same command. There would not be any need TL> for that rule if selecting from the same table you're inserting into TL> were flat-out forbidden, as the MySQL docs claim. TL> regards, tom lane -- Mit freundlichen Grüssen Evil Azraelmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] inet versus text for ip addresses
The PostgreSQL inet datatype stores an holds an IP host address, and optionally the identity of the subnet it is in, all in one field. This requires 12 bytes. Using my "random" data of approximately 8000 IP addresses collected during previous polls, I've found the average length of an IP address is 13.1 bytes.An integer requires 4 bytes. First question: Why not store an option to store just an IP address? That should require less than the 12 bytes for inet. On to the real question: The existing tables are: create table recount_ips ( ipidserialnot null, ipaddress inet not null, primary key (ipid) ); create unique index recount_ips_ip_address on recount_ips (ipaddress); create table recount_iptopolls ( pollid integer not null, ipidinteger not null, primary key (pollid, ipid) ); alter table recount_iptopolls add foreign key (pollid) references recount_polls (pollid) on update restrict on delete restrict; alter table recount_iptopolls add foreign key (ipid) references recount_ips (ipid) on update restrict on delete restrict; I think a better solution is one table: create table recount_iptopolls ( pollid integer not null, ipaddress inet not null, primary key (pollid, ipaddress) ); alter table recount_iptopolls add foreign key (pollid) references recount_polls (pollid) on update restrict on delete restrict; It removes a table and the associated primary key, and removed a foreign key from the modified recount_iptopolls table. Comments? -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
