AW: INSERT with SELECT on same table
Thanks for the response, but doing this results in : ERROR 1093: INSERT TABLE 'myTable' isn't allowed in FROM table list marc -Ursprüngliche Nachricht- Von: Nathan [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 14. November 2001 19:05 An: Votteler Marc Betreff: Re: INSERT with SELECT on same table Maybe I'm not looking at it hard enough :-) but could this work: INSERT INTO myTable (myCol) SELECT DISTINCT '' FROM myTable MT WHERE MT.myCol != '' I don't have a test table setup to try this, but maybe it will work for you? I'm not totally sure I understand the intent, so if I'm off, please explain more and I will try to help! Cheers, # Nathan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: INSERT with SELECT on same table
Thanks for the response, you are right I would get an Exception describing that the record already exists, but because I do not know which DBMS is being used (one of Oracle, MS SQL and mySQL), I can not discern if it was a 'real' error or just the fact that the record already exists. marc -Ursprüngliche Nachricht- Von: Rick Emery [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 14. November 2001 19:46 An: Votteler Marc; [EMAIL PROTECTED] Betreff: RE: INSERT with SELECT on same table If one of the table's fields is defined as UNIQUE or PRIMARY KEY, it will allow the insertion only once with that key. When a second attempt is made to insert the record with the same key, the insertion will be ignored and you may ignore the error code that results. Would that work for you? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: INSERT with SELECT on same table
insert-select on the same table is not allowed in MySQL. If I remember right it isn't allowed in ANSI SQL either, supposedly because it could lead to infinite loops if implemented poorly. Jon Gardiner. -Original Message- From: Votteler Marc [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 3:30 AM To: Rick Emery; [EMAIL PROTECTED] Subject: RE: INSERT with SELECT on same table Thanks for the response, you are right I would get an Exception describing that the record already exists, but because I do not know which DBMS is being used (one of Oracle, MS SQL and mySQL), I can not discern if it was a 'real' error or just the fact that the record already exists. marc -Ursprüngliche Nachricht- Von: Rick Emery [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 14. November 2001 19:46 An: Votteler Marc; [EMAIL PROTECTED] Betreff: RE: INSERT with SELECT on same table If one of the table's fields is defined as UNIQUE or PRIMARY KEY, it will allow the insertion only once with that key. When a second attempt is made to insert the record with the same key, the insertion will be ignored and you may ignore the error code that results. Would that work for you? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: INSERT with SELECT on same table
insert-select on the same table is not allowed in MySQL. If I remember right it isn't allowed in ANSI SQL either, supposedly because it could lead to infinite loops if implemented poorly. Jon Gardiner. Core SQL support does not require the DBMS to support it; enhanced SQL support allows for it. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT with SELECT on same table
Hi there, I am currently facing the following problem and hope someone can help me. I want to make an INSERT into a Table only if I haven't done this before. This is because the INSERT can be done by several Programs and the record should only be inserted once. I have found the following solutions (but none of them was satisfying): 1. First do a SELECT and the make an INSERT if necessary. 2. Doing the following works fine with MS SQL and Oracle but not with mySQL: insert into myTable (myCol) select distinct '' from myTable where '' not in (select myCol from myTable ); The error I get is ERROR 1066: Not unique table/alias: 'af_events' That's it. Hope someone can help me. Thanks in advance marc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: INSERT with SELECT on same table
If one of the table's fields is defined as UNIQUE or PRIMARY KEY, it will allow the insertion only once with that key. When a second attempt is made to insert the record with the same key, the insertion will be ignored and you may ignore the error code that results. Would that work for you? -Original Message- From: Votteler Marc [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 14, 2001 11:01 AM To: [EMAIL PROTECTED] Subject: INSERT with SELECT on same table Hi there, I am currently facing the following problem and hope someone can help me. I want to make an INSERT into a Table only if I haven't done this before. This is because the INSERT can be done by several Programs and the record should only be inserted once. I have found the following solutions (but none of them was satisfying): 1. First do a SELECT and the make an INSERT if necessary. 2. Doing the following works fine with MS SQL and Oracle but not with mySQL: insert into myTable (myCol) select distinct '' from myTable where '' not in (select myCol from myTable ); The error I get is ERROR 1066: Not unique table/alias: 'af_events' That's it. Hope someone can help me. Thanks in advance marc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
