sql to duplicate records with modified value

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@domain.tld'
maildir 'domain.tld/username@domain.tld/'

add new record that has:
user 'username+spam@domain.tld'
maildir 'domain.tld/username@domain.tld/.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=gcdmg-mysql-2@m.gmane.org

Re: sql to duplicate records with modified value

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@domain.tld'
> maildir 'domain.tld/username@domain.tld/'
>
> add new record that has:
> user 'username+spam@domain.tld'
> maildir 'domain.tld/username@domain.tld/.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)
>
>
>
Are you hoping to do all that you want - copy rows, update rows and
create new rows - in a single SQL statement? Because if that's what you
want, I don't think it's possible. Unless someone has come up with some
new tricks, you can't insert a new record and update an existing one
within the same SQL statement.

If you want to do it via several SQL statements, each part of what you
want should be possible via different statements in a script where you
can use the script itself to help with the update logic. If you want to
do the updates from the command line only and won't consider a program,
I don't know how to do it.

I should point out that I'm quite fluent in SQL and have been writing it
for a long time. I haven't used MySQL in a few years now but I'm still
on the mailing list and your question caught my eye.


--
Rhino

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: sql to duplicate records with modified value

> Voytek Eymont wrote:

> Are you hoping to do all that you want - copy rows, update rows and
> create new rows - in a single SQL statement? Because if that's what you
> want, I don't think it's possible. Unless someone has come up with some
> new tricks, you can't insert a new record and update an existing one
> within the same SQL statement.
>
> If you want to do it via several SQL statements, each part of what you
> want should be possible via different statements in a script where you can
> use the script itself to help with the update logic. If you want to do the
> updates from the command line only and won't consider a program, I don't
> know how to do it.
>
> I should point out that I'm quite fluent in SQL and have been writing it
> for a long time. I haven't used MySQL in a few years now but I'm still on
> the mailing list and your question caught my eye.

Rhino, thanks

a script would be great, if you can help with it, I'd be grateful.

I'm not sure where/how to start, the username is a primary index.

or do I run a dump, then, work some script over dump, and, re-import ?


--
Voytek


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: sql to duplicate records with modified value

--=====================_54555578==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

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@domain.tld'
>maildir 'domain.tld/username@domain.tld/'
>
>add new record that has:
>user 'username+spam@domain.tld'
>maildir 'domain.tld/username@domain.tld/.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=stuff@tweek.com.au

( 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
--=====================_54555578==.ALT--

Re: sql to duplicate records with modified value

thanks, Ray,

that worked well

(btw, you have a typo, 'Independant' instead of 'Independent')

btw2, I have a pdf with some 15,000 names that I would like to display
with a search function, I email you later, maybe you can help me with that


--
Voytek



> 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@domain.tld' maildir
>> 'domain.tld/username@domain.tld/'
>>
>>
>> add new record that has: user 'username+spam@domain.tld' maildir
>> 'domain.tld/username@domain.tld/.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=stuff@tweek.com.au
>>
>
> ( 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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org