List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 27 2010 12:42pm
Subject:Re: sql to duplicate records with modified value
View as plain text  

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.


--
Rhino
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