Importing files as elements
Hi all, I would like to populate one column of my table with XML files - meaning each element in that column will be an XML file. Is this possible and how can it be done? Would it be more efficient to store the files in the filesystem and instead keep a set of pointers as elements in that column that point to those files? Thanks very much -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sql to duplicate records with modified value
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 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@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=st...@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
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=arch...@jab.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 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@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=arch...@jab.org