Re: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

2005-08-19 Thread Michael Stassen 

Thomas Spahni wrote:

Hi suomi,

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

Regards,
Thomas Spahni




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

INSERT INTO duprows SELECT * FROM duptemp;



So long as you replace 'id' with the name of your primary key column, this 
has the advantage of working regardless of which column is the primar key. 
If we can also assume the primary key is in column 1, then we can shorten 
this slightly:


  CREATE TEMPORARY TABLE duptemp SELECT * FROM duprows WHERE id = 2;
  ALTER TABLE duptemp DROP COLUMN id;

  INSERT INTO duprows SELECT NULL, duptemp.* FROM duptemp;

again replacing 'id' with the name of your pk column.

Michael

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

Re: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

2005-08-19 Thread Thomas Spahni 
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
NULLsome 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]

RE: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

2005-08-19 Thread Gordon Bruce 
If you want to have all values except the primary key be the same and
say your is foo_ID
You can simply do 
INSERT INTO foo 
  (foo_ID... {rest of columns list}) 
SELECT new primary key value,
   {rest of columns list}
FROM   foo 
WHERE  foo_ID = {primary key value of row you want to copy}

If your PRIMARY KEY is an auto_increment field, just omit foo_ID from
the columns list in both the INSERT and SELECT.

-Original Message-
From: suomi [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 19, 2005 7:08 AM
To: mysql@lists.mysql.com
Subject: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key

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]




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