Hi suomi,

it can be done with a temporary table. See the following example.

Regards,
Thomas Spahni


USE test;

CREATE TABLE duprows (
        id INT NULL AUTO_INCREMENT PRIMARY KEY,
        content VARCHAR(255)
) TYPE=MyISAM;

INSERT INTO duprows VALUES(NULL, 'some text'),(NULL, 'some other text');

SELECT * FROM duprows;

CREATE TEMPORARY TABLE duptemp SELECT * FROM duprows WHERE id = 2;
ALTER TABLE duptemp CHANGE id id INT NULL;
UPDATE duptemp SET id = NULL;

SELECT * FROM duptemp;

INSERT INTO duprows SELECT * FROM duptemp;

SELECT * FROM duprows;


yields:

id      content
1       some text
2       some other text

id      content
NULL    some other text

id      content
1       some text
2       some other text
3       some other text




On Fri, 19 Aug 2005, suomi wrote:

> Hi listers
> I once asked if there is an SQL syntax permitting to copy a row in the
> same table. I got no answer, so there is no such syntax.
>
> now i meant to have found a work-around using (see subject).
>
> problem is, that when i do a SELECT * ... INTO OUTFILE .. i will also
> catch the PRIMARY KEY column if there is one and the LOAD DATA INFILE
> ... of this file will fail because of duplicate keys. i tried to use the
> FOREIGN_KEY_CHECKS=0 but obiousely this works on foreign keys not on the
> primary key.
>
> certainly, i can very very clumsily construct a SELECT at1, ... atn INTO
>   OUTFILE statement which selects all columns except the primary key.
>
> the REPLACE and IGNORE constructs are not what i want either, because i
> want to add a row in any case, not replace an existing one nore ignore
> the action.
>
> is there a more elegant way then the clumsy making of an attr list,
> which includes alle columns except the primary key column?
>
> thanks very much for your interest and understanding.
>
> suomi
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to