Re: sql to duplicate records with modified value ?

Assuming your table is called "users" and your columns are "user" and
"maildir":

INSERT INTO users(user,maildir) SELECT REPLACE(user,'@','+spam@') AS user,
CONCAT(maildir,'.spam') AS maildir FROM users;

On Sat, Mar 27, 2010 at 3:18 PM, Voytek Eymont <[email protected]> wrote:

> I have virtual mailboxes in MySQL table like below:
>
> I'd like to duplicate all records whilst MODIFYING two fields like so:
>
> current record has format like:
> user '[email protected]'
> maildir 'domain.tld/[email protected]/'
>
> add new record that has:
> user '[email protected]'
> maildir 'domain.tld/[email protected]/.spam/'
>
> so that I'll end up with two record, existing, plus new one
>
> field 'user' - insert '+spam' ahead of '@'
> field 'maildir' append '.spam/'
>
> what's the best way ?
>
>
> mysql  Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3
> Server version:         4.1.22-standard
>
> mysql> show tables;
> +-----------------------+
> | Tables_in_postfix     |
> +-----------------------+
> | admin                 |
> | alias                 |
> | config                |
> | domain                |
> | domain_admins         |
> | fetchmail             |
> | log                   |
> | mailbox               |
> | vacation              |
> | vacation_notification |
> +-----------------------+
> 10 rows in set (0.00 sec)
>
>
> --
> Voytek
>
> --
> SLUG - Sydney Linux User Group Mailing List - http://slug.org.au/
> Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
>

-- 
SLUG - Sydney Linux User Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html