Forum Moderators: open

Message Too Old, No Replies

MySQL: copy field to other fields

MySQL: copy field to other fields

         

Das Capitolin

3:36 pm on Sep 8, 2008 (gmt 0)

10+ Year Member



In my SQL database I have about 5000 content items with the table structure below. Each of these content items has a title, but the metadesc and metakey fields have been left blank. I would like to populate these fields with the unique title. Is there a simple method to copy 'title' to the 'metadesc' and 'metakey' fields for each individual content item?

Here is the table structure for each content item:

--
-- Table structure for table `jos_content`
--

CREATE TABLE IF NOT EXISTS `jos_content` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(100) character set latin1 collate latin1_german2_ci NOT NULL default '',
`title_alias` varchar(100) character set latin1 collate latin1_german2_ci NOT NULL default '',
`introtext` mediumtext character set latin1 collate latin1_german2_ci NOT NULL,
`fulltext` mediumtext character set latin1 collate latin1_german2_ci NOT NULL,
`state` tinyint(3) NOT NULL default '0',
`sectionid` int(11) unsigned NOT NULL default '0',
`mask` int(11) unsigned NOT NULL default '0',
`catid` int(11) unsigned NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` int(11) unsigned NOT NULL default '0',
`created_by_alias` varchar(100) character set latin1 collate latin1_german2_ci NOT NULL default '',
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
`modified_by` int(11) unsigned NOT NULL default '0',
`checked_out` int(11) unsigned NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_up` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_down` datetime NOT NULL default '0000-00-00 00:00:00',
`images` text character set latin1 collate latin1_german2_ci NOT NULL,
`urls` text character set latin1 collate latin1_german2_ci NOT NULL,
`attribs` text character set latin1 collate latin1_german2_ci NOT NULL,
`version` int(11) unsigned NOT NULL default '1',
`parentid` int(11) unsigned NOT NULL default '0',
`ordering` int(11) NOT NULL default '0',
`metakey` text character set latin1 collate latin1_german2_ci NOT NULL,
`metadesc` text character set latin1 collate latin1_german2_ci NOT NULL,
`access` int(11) unsigned NOT NULL default '0',
`hits` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `idx_access` (`access`),
KEY `idx_state` (`state`),
KEY `idx_catid` (`catid`),
KEY `idx_mask` (`mask`),
KEY `idx_created_by_alias` (`created_by_alias`),
KEY `idx_sectionid` (`sectionid`),
KEY `idx_checked_out` (`checked_out`)

ZydoSEO

5:58 pm on Sep 8, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



If you simply want to copy the title of each row into the corresponding metadesc and metakey fields of the same row, the following should work:

UPDATE jos_content
SET metadesc = title, metakey = title

Das Capitolin

6:17 pm on Sep 8, 2008 (gmt 0)

10+ Year Member



ZydoSEO, you sir are a genius.

I can't believe how easy that solution was. Thank you sincerely.

Das Capitolin

6:24 pm on Sep 8, 2008 (gmt 0)

10+ Year Member



Curious though, what if I only wanted to do this on records 200-5000 (exclude the first 200)?

ZydoSEO

7:51 pm on Sep 8, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



That would depend on which type of DB you're using. I'm guessing it's MySQL since most here use it, but the approach might be different for Oracle, SQL*Server, or others. I have no experience w/ MySQL... only MS SQL*Server and Oracle. Some DBs have a query result ROWID or ROWNUM. I'm not sure what (if anything) is available along these lines for MySQL.

If there is some condition under which you'd like to make the change then you simply put that in the WHERE clause. For instance, if you update anything with created after '2005-01-01' you might do something like:

UPDATE jos_content
SET metadesc = title, metakey = title
WHERE created >= '2005-01-01 00:00:00'

Sorry I can't be more help.

Das Capitolin

3:28 am on Sep 9, 2008 (gmt 0)

10+ Year Member



ZydoSEO, thank you very much for your assistance! I sincerely appreciate that you have taken time to reply to my problem, and supply a solution.

For others, this is what I did:

UPDATE jos_content
SET metadesc = title, metakey = title
WHERE id >= '220'

This copied the title into the metadesc (meta description) and metakey (meta keyword) sections for items #220 and higher.