Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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`
share|improve this answer
    
Sorry, edited the query now. –  hjpotter92 Mar 30 '13 at 20:29
    
i'm edited too, it's work.. –  mehdi Mar 30 '13 at 20:39
    
Thank you! Can you also help me with my second question: "Can I extract the date from there and use it with STR_TO_DATE() to pass it in filldate field too?" –  hjpotter92 Mar 30 '13 at 21:23
    
yes : STR_TO_DATE(date, '%m/%d/%Y') => replace with date in select query –  mehdi Mar 30 '13 at 21:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.