Copying values from one column to another MySQL

Posted by admin at December 4, 2015

The other day, I had a little challenge with some aviation checklist items that I was coding into a MySQL table. I had created the table and added the checklist items successfully, however I made an error. The checklist items table comprised of the following fields:


CREATE TABLE IF NOT EXISTS `checklist_items` (
`checklist_item_id` int(11) NOT NULL,
`checklist_id` int(11) NOT NULL COMMENT 'The Checklist record ID that owns this checklist items',
`checklist_item_title` mediumtext NOT NULL COMMENT 'Checklist item title',
`checklist_item_details` mediumtext COMMENT 'Details to the checklist item',
`is_markable` tinyint(2) DEFAULT '1' COMMENT 'Checklist item can be marked and is not a title'
...
) ENGINE=MyISAM AUTO_INCREMENT=213 DEFAULT CHARSET=utf8;

The field `checklist_item_title` should provide a clear, simple instruction on the what is to be checked, like an inspection item or a requirement, while the `checklist_item_details` should provide more information to the checklist item. The error? I had created a set of checklist items, added brief and scanty information in the titles field of the table and then left the actual checklist items titles in the details section.

This means that I had Checklist titles that had words like ‘Chapter 9’ or ‘Element 4.1.’ etc, while the actual titles that I wanted to use for my applications was stuck in the details section of the table. So I needed to copy the details field values onto the titles values without loosing the current contents of the title values.

1. The plan was to create a new buffer row in the table:


-- add new buffer fields for the CHKLST table

ALTER TABLE `t_chklst`
ADD `buffer` VARCHAR(200) NULL DEFAULT 0 COMMENT 'Temporary field to hold field values' AFTER `checklist_item_details`;

2. Copy all the contents of the titles field to the buffer field. Copy values the belong to checklist items under the Checklist with the ID 8 “Safety Management Systems”


-- copy the ALL the contents of the titles field to the buffer field

UPDATE `t_checklist_items` SET
`buffer` = `checklist_item_title`
WHERE 1
AND `checklist_id` = "8"

3. Next copy the contents of the details field to the title field. Remember to restrict copying to only checklist items that belong to checklist 8.


-- copy the ALL the contents of the titles field to the buffer field

UPDATE `t_checklist_items` SET
`checklist_item_title` = `checklist_item_details`
WHERE 1
AND `checklist_id` = "8"

4. Then I wanted to also keep the former contents of the title, now stored in the title, by prepending the them to the contents of the details and save the resulting text in the details field.


    buffer_value + ': ' + details_value

So I replaced the contents of the details field with the contents of the buffer and a separator string. Bear in mind that at this point, the contents of the details field is also available in the title field (see step 3 above).


-- copy the ALL the contents of the titles field to the buffer field

UPDATE `t_checklist_items` SET
`checklist_item_details` = CONCAT(`buffer`, ': ')
WHERE 1
AND `checklist_id` = "8"
AND `buffer` <> ""

After this, the title and details fields have interchanged values, somewhat. Also notice that I made sure that the buffer value is not NULL. (This will make sure not to copy empty fields values)

5. Finally, we finish off the process by joining the contents of the title and details field and saving in the details field:


-- copying the columns on the cheklist items

UPDATE `jos_ion_cts_isats_chklist_items` SET
`chklist_item_details` = CONCAT(`chklist_item_details`, `chklist_item_title`)
WHERE 1
AND `checklist_id` = "8"

Notice that the details fields came before the title.

   0 likes

Suggested Read