Insert from one row to another using cases

Question

My original table was(being used since 2005):

CREATE TABLE `request` (
    `msg` VARCHAR(150) NOT NULL,
    `id` VARCHAR(20) NOT NULL,
    `ctg` VARCHAR(10) NOT NULL DEFAULT 'misc',
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `status` VARCHAR(10) NOT NULL DEFAULT 'empty',
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    UNIQUE INDEX `msg_id` (`msg`, `id`),
    UNIQUE INDEX `msg_ctg` (`msg`, `ctg`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
CHECKSUM=1
AUTO_INCREMENT=18491;

Since, it was neither good management of table nor appreciable by me, I created a newer table:

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    `filledby` VARCHAR(32) NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

What I want to do now is:

SELECT data from first table and insert as follows:

• msg to msg, id to nick, ctg to ctg and date to dated are copied as is.
• status in original table is either empty or filled. I want to use a CASE clause while insertion so that Y and N are inserted accordingly.

My query:

INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )
SELECT `fid`,
    `ctg`, 
    `msg`, 
    `id`, 
    `date`,
    CASE
        WHEN `status` = 'empty' THEN 'N'
        WHEN `status` = 'filled' THEN 'Y'
    END CASE
FROM `request`

My question is

Is the query above perfect? Also, one of the sample string from request table with 'filled' status is like:

.hack//SIGN (filled by hjpotter92 in 02/08/12 09:47:07 )

the format is the same: filled by <<nick>> in <<<date>> in <<mm/dd/yy H:I:S>> for all filled entries. Can I extract the date from there and use it with STR_TO_DATE() to pass it in filldate field too? I know it'd need me to create a procedure/function.

For now, I am thinking of using PHP for the task but I wanted a clean MySQL solution if possible.

Answer 1

so it's work for me..

INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )
SELECT `fid`,
    `ctg`, 
    `msg`, 
    `id`, 
    `date`,
    CASE `status`
        WHEN 'empty' THEN 'N'
        WHEN 'filled' THEN 'Y'
    END
FROM `request`