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)
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.