copy rows from a table and inserting into same table but changing one field

[kevinkhan]

Any ideas how i can do this?

 

The structure of my database is

 

CREATE TABLE IF NOT EXISTS `Classified` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user` varchar(255) COLLATE latin1_general_ci NOT NULL,

  `description` text COLLATE latin1_general_ci NOT NULL,

  `link` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `img1` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `published` tinyint(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1642 ;

 

and all i wanna do is copy all rows that have a certain email we say email1@hotmail.com and insert them back into the same table but changing the email to email2@hotmail.com

 

[MrXHellboy]

Why remove and then put them back into the table ? Why not use UPDATE

http://dev.mysql.com/doc/refman/5.0/en/update.html

 

mysql_query("UPDATE table SET field1='blabla'");

[kevinkhan]

sorry i wasnt clear in my description.

 

What i wanna do it is duplicate the records and only change the emails.

 

so i want to keep the old records and also want the same records but with a different email.

 

the user field is where im storing the email addresses.

 

Thanks for your help

[fenway]

What rules are you using to make this determination?

[jdavidbakr]

Something like this?  Assuming the link column contains your e-mail address (since you don't have an e-mail column defined)

 

insert into Classified
select NULL, user, description, 'email2@hotmail.com', img1, published
where link like 'email1@hotmail.com'

 

The NULL for the PK will put in the next auto-increment value, the other columns will be filled by the matching rows except for the 4th (link) which will be replaced by the new e-mail address.