List:General Discussion« Previous MessageNext Message »
From:Ray Cauchi Date:March 27 2010 1:27pm
Subject:Re: sql to duplicate records with modified value
View as plain text  
Hi Voytek

You could try some variation of:

INSERT INTO inserttable (user, maildir)
SELECT REPLACE(user, '@', 'spam@') as user, CONCAT(maildir,'.spam/') 
as maildir
FROM  selecttable
[WHERE ..]

the where bit is optional of course!

let me know how you go - hope you are keeping well!

ray

At 03:17 PM 27/03/2010, Voytek Eymont wrote:
>I have Postfix 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 'username@stripped'
>maildir 'domain.tld/username@stripped/'
>
>add new record that has:
>user 'username+spam@stripped'
>maildir 'domain.tld/username@stripped/.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
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

( T W E E K ! )

PO Box 15
Wentworth Falls
NSW Australia 2782

| p:    +61 2 4702 6377 (Sydney/Penrith/Blue Mountains)
| p:    +61 2 4915 8532 (Newcastle/Hunter)
| f:    +61 2 8456 5743
| m:    0414 270 400
| e:    ray at tweek dot com dot au
| w:    www dot tweek dot com dot au

'What is more immoral than war?' - Marquis de Sade  
Thread
sql to duplicate records with modified valueVoytek Eymont27 Mar
  • Re: sql to duplicate records with modified valueRhino27 Mar
    • Re: sql to duplicate records with modified valueVoytek Eymont27 Mar
  • Re: sql to duplicate records with modified valueRay Cauchi27 Mar
    • Re: sql to duplicate records with modified valueVoytek Eymont28 Apr