sabecs
asked on
MySQL - I need to duplicate a record in same table, but alter some fields?
MySQL - I need to duplicate a record in same table, but alter some fields?
Is there a way to duplicate say record 782 and alter the value of `image1` ,`image2` ,`image3` ,`image4` & `image5` WHERE none of these fields contain nopic.jpg?
If I was duplicating the record with id 782, I need the new value of `image1` column to be equal to "/uploads/item1063_1_copy.
Thanks in advance for your feedback.
Is there a way to duplicate say record 782 and alter the value of `image1` ,`image2` ,`image3` ,`image4` & `image5` WHERE none of these fields contain nopic.jpg?
If I was duplicating the record with id 782, I need the new value of `image1` column to be equal to "/uploads/item1063_1_copy.
Thanks in advance for your feedback.
--
-- Table structure for table `items`
--
CREATE TABLE IF NOT EXISTS `items` (
`id` int(11) NOT NULL auto_increment,
`item_type` enum('G','S','F') default 'F',
`min_order` tinyint(3) default '1',
`prodcode` varchar(10) default NULL,
`true_stock_count` varchar(200) default '0',
`display_stock_count` varchar(200) default '0',
`low_stock_count` varchar(40) default '0',
`date_time_expires` varchar(120) default NULL,
`limit_availability` varchar(20) default NULL,
`itemName` varchar(50) default NULL,
`itemDesc` text,
`itemDesc2` text,
`itemDesc3` text,
`weight` decimal(12,2) NOT NULL default '0.00',
`height` mediumint(8) NOT NULL,
`length` mediumint(8) NOT NULL,
`width` mediumint(8) NOT NULL,
`combined_postage` int(3) default '1',
`item_postage` varchar(120) default NULL,
`item_postage_amt` double(4,2) default NULL,
`aust_post_estimate` varchar(240) default 'Regular Parcel - $6.60 Express Post - $9.55',
`tabs_needed` tinyint(3) default '0',
`tabhead1` varchar(60) default 'Description',
`tabhead2` varchar(60) default NULL,
`tabhead3` varchar(60) default NULL,
`itemDescb` varchar(40) default NULL,
`itemPrice` decimal(8,2) default '0.00',
`retPrice` decimal(8,2) default '0.00',
`specialPrice` decimal(8,2) default '0.00',
`specs1` varchar(180) default NULL,
`spechead1` varchar(180) default NULL,
`specs2` varchar(180) default NULL,
`spechead2` varchar(180) NOT NULL,
`add_comments` char(1) default 'N',
`more_details` enum('Y','N') default 'N',
`image1` varchar(60) default '/uploads/nopic.jpg',
`image2` varchar(60) default '/uploads/nopic.jpg',
`image3` varchar(60) default '/uploads/nopic.jpg',
`image4` varchar(60) default '/uploads/nopic.jpg',
`image5` varchar(60) default '/uploads/nopic.jpg',
`cookie` varchar(50) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1062 ;
--
-- Dumping data for table `items`
--
INSERT INTO `items` (`id`, `item_type`, `min_order`, `prodcode`, `true_stock_count`, `display_stock_count`, `low_stock_count`, `date_time_expires`, `limit_availability`, `itemName`, `itemDesc`, `itemDesc2`, `itemDesc3`, `weight`, `height`, `length`, `width`, `combined_postage`, `item_postage`, `item_postage_amt`, `aust_post_estimate`, `tabs_needed`, `tabhead1`, `tabhead2`, `tabhead3`, `itemDescb`, `itemPrice`, `retPrice`, `specialPrice`, `specs1`, `spechead1`, `specs2`, `spechead2`, `add_comments`, `more_details`, `image1`, `image2`, `image3`, `image4`, `image5`, `cookie`) VALUES
(782, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'IAMS Smart Puppy Large Breed', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 20px; font-family: Arial, Helvetica-Normal, serif; font-size: 14px; font-weight: bold;">IAMS Smart Puppy Large Breed</span><br />\r\n <br />\r\n <strong><span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">Weight:3.6KG<br />\r\n 9KG +$32<br />\r\n 18KG +$74</span></strong><br />\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;"> <br />\r\n This Iams ProActive Health formula has PreBiotics that work inside the digestive tract to promote healthy digestion and strong defenses. Healthy inside. Healthy outside.</span></p>\r\n<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">Designed for dogs ages 1-24 months,Iams concentrated nutrition proactively nourishes your large-breed puppy to help keep him at his best.</span></p>\r\n<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">Natural fish oils with DHA (Docosahexaenoic Acid), a nutrient vital for optimal brain development, promote smarter, more trainable puppies. Protein sourced from chicken and egg helps build strong, firm muscles. Seven essential nutrients nourish the heart while essential minerals promote strong teeth and bones, and antioxidants help maintain a strong immune system.</span></p>\r\n<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">A high-quality protein with no fillers or artificial preservatives, Iams is gentle on the digestive system. Fuel your puppy’s healthy development with 100% complete and balanced nutrition that’s vet recommended and 100% Satisfaction Guaranteed.</span></p>\r\n', NULL, NULL, 4.00, 15, 83, 41, 1, 'flat_rate', 20.00, ' Regular Parcel - $23.30 Express Post - $70.10', 1, 'Description', NULL, NULL, NULL, 25.95, NULL, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item782_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', 'uploads/nopic.jpg', NULL),
(762, NULL, NULL, NULL, '48,24,22,8,12,9', '48,24,22,8,12,9', '0', '29/12/2012 04:25 ', 'TO', 'Font Line Plus for Cats', '<p>\r\n <span style="line-height: 20px; font-family: Arial, Helvetica-Normal, serif; color: rgb(51,51,51); font-size: 14px; font-weight: bold"><span style="margin-top: 55px">Font Line Plus for Cats</span></span></p>\r\n', NULL, NULL, 16.00, 21, 47, 19, 3, 'aust_post', 0.00, ' Regular Parcel - $26.15 Express Post - $83.30', 1, 'Description', NULL, NULL, NULL, 44.00, NULL, 33.00, '5kg,6kg,8kg', 'Weight', 'red,blue,green', 'Colour', 'N', NULL, '/uploads/item762_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', 'uploads/nopic.jpg', NULL),
(768, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'Advance Adult Dog Small Breed', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 20px; font-family: Arial, Helvetica-Normal, serif; font-size: 14px; font-weight: bold;">Advance Adult Dog Small Breed</span></p>\r\n<p>\r\n <strong><span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">Weight: 1.5KG<br />\r\n 2.5KG +$8.85<br />\r\n 7KG +$41.15<br />\r\n 13KG +$77.80</span></strong><br />\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;"> <br />\r\n Adult maintenance food ideal for dogs of normal activity level<br />\r\n Turkey and Rice as main source of high quality protein<br />\r\n Beneficial in managing dietary sensitivity<br />\r\n Formulated to help maintain urinary tract health<br />\r\n Smaller kibble size ideal for toy to medium sized dogs<br />\r\n High palatability to tempt fussier eaters </span></p>\r\n', NULL, NULL, 1.50, 5, 16, 16, 1, 'aust_post', 34.55, ' Regular Parcel - $12.85 Express Post - $21.70', 1, 'Description', NULL, NULL, NULL, 16.40, NULL, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item768_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', 'uploads/nopic.jpg', NULL),
(772, NULL, NULL, NULL, '0', '0', '0', '30/06/2012 00:00 ', 'TO', 'Advance Dog Dental Large and Giant Breeds', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 20px; font-family: Arial, Helvetica-Normal, serif; font-size: 14px; font-weight: bold;">Advance Dog Dental Large and Giant Breeds</span><br />\r\n <br />\r\n <strong><span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">Weight: 3KG<br />\r\n 8KG +$39.20<br />\r\n 15KG +$84.20</span></strong><br />\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;"> <br />\r\n For Large and Giant sized Breeds, has specifically designed larger kibble and active ingredients which help to promote and maintain dental health. </span></p>\r\n', NULL, NULL, 0.50, 5, 3, 5, 1, 'free_postage', 0.00, 'Regular Parcel - $6.60 Express Post - $9.55', 1, 'Description', NULL, NULL, NULL, 35.05, NULL, 24.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item772_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', 'uploads/nopic.jpg', NULL),
(786, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'Science Diet® - Puppy Small Bites', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 20px; font-family: Arial, Helvetica-Normal, serif; font-size: 14px; font-weight: bold;">Science Diet® - Puppy Small Bites</span><br />\r\n <br />\r\n <strong><span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">Rich in fish oil and nourishing fatty acids.</span></strong></p>\r\n<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: Arial, Helvetica-Normal, serif; font-size: 12px;">Hill''s® Science Diet® Puppy Small Bites dog food provides precisely balanced nutrition to build immunity and enhance mobility for puppies who prefer a smaller kibble. It is enriched with DHA from high quality fish oil and made with high quality ingredients that provide an ideal combination of nutrients, clinically proven antioxidants and vital fatty acids to give your puppy the best start in life.</span></p>\r\n<p>\r\n <br />\r\n </p>\r\n', NULL, NULL, 5.00, 17, 12, 17, 3, 'aust_post', 0.00, ' Regular Parcel - $15.70 Express Post - $34.90', 1, 'Description', NULL, NULL, NULL, 25.00, NULL, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item786_1.jpg', '/uploads/item786_2.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', 'uploads/nopic.jpg', NULL),
(1019, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'IAMS ', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 20px; font-family: verdana,arial,helvetica-normal,serif; font-size: 18px; font-weight: normal;">Cat Food</span></p>\r\n', NULL, NULL, 0.50, 29, 10, 8, 1, 'flat_rate', 66.30, ' Regular Parcel - $6.60 Express Post - $9.55', NULL, 'Description', NULL, NULL, NULL, 56.88, NULL, 46.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item1019_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(936, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, NULL, NULL, NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', NULL, 'Description', NULL, NULL, NULL, 0.00, 0.00, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item936_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(872, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'Gold Listing', '<p>\r\n <span style="color: rgb(50, 186, 242); line-height: 20px; font-family: verdana, arial, helvetica-normal, serif; font-size: 18px; font-weight: normal;">12 months Gold Listing</span></p>\r\n', NULL, NULL, 0.50, 5, 3, 5, 1, 'flat_rate', 17.00, 'Regular Parcel - $6.60 Express Post - $9.55', 1, 'Description', NULL, NULL, NULL, 299.00, NULL, 39.99, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item872_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(874, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'Silver Membership', '<p>\r\n <span style="color: rgb(50, 186, 242); line-height: 20px; font-family: verdana, arial, helvetica-normal, serif; font-size: 18px; font-weight: normal;">Silver Membership</span></p>\r\n', NULL, NULL, 0.50, 5, 3, 5, 1, 'aust_post', 0.00, ' Regular Parcel - $6.60 Express Post - $9.55', 1, 'Description', NULL, NULL, NULL, 99.00, NULL, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item874_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(876, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'Free Membership', '<p>\r\n <span style="line-height: 20px; font-family: verdana, arial, helvetica-normal, serif; color: rgb(51,51,51); font-size: 18px; font-weight: normal">Free Membership - 12 months </span></p>\r\n<p>\r\n <span style="line-height: 16px; font-family: verdana, arial, helvetica-normal, serif; color: rgb(51,51,51); font-size: 14px; font-weight: normal">Directory Listing - List your business with us for FREE</span></p>\r\n', NULL, NULL, 0.50, 5, 3, 5, 10, 'aust_post', 0.00, ' Regular Parcel - $6.60 Express Post - $9.55', 1, 'Description', NULL, NULL, NULL, 0.00, NULL, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item876_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(882, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'Bird Food', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 20px; font-family: verdana, arial, helvetica-normal, serif; font-size: 18px; font-weight: normal;">Bird Food</span></p>\r\n', NULL, NULL, 0.50, 5, 3, 5, 1, 'flat_rate', 5.00, 'Regular Parcel - $6.60 Express Post - $9.55', 1, 'Description', NULL, NULL, NULL, 55.00, NULL, 45.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item882_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(908, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, 'Koi Balance', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 20px; font-family: verdana, arial, helvetica-normal, serif; font-size: 18px; font-weight: normal;">Koi Balance - fish Food</span></p>\r\n', NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', 1, 'Description', NULL, NULL, NULL, 55.00, 0.00, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item908_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(886, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, 'Gallery Image', '<p>\r\n <span style="line-height: 20px; font-family: verdana, arial, helvetica-normal, serif; color: rgb(51,51,51); font-size: 18px; font-weight: normal">Test Gallery</span></p>\r\n', NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', 1, 'Description', NULL, NULL, NULL, 55.00, 0.00, 23.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item886_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(926, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, NULL, NULL, NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', NULL, 'Description', NULL, NULL, NULL, 0.00, 0.00, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item926_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(928, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, NULL, NULL, NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', NULL, 'Description', NULL, NULL, NULL, 0.00, 0.00, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item928_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(930, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, 'Test', NULL, NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', NULL, 'Description', NULL, NULL, NULL, 0.00, 0.00, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item930_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(932, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, NULL, NULL, NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', NULL, 'Description', NULL, NULL, NULL, 0.00, 0.00, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item932_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(934, 'F', NULL, NULL, '0', '0', '0', NULL, NULL, 'Test', NULL, NULL, NULL, 0.00, 0, 0, 0, 1, NULL, NULL, 'Regular Parcel - $6.60 Express Post - $9.55', NULL, 'Description', NULL, NULL, NULL, 0.00, 0.00, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item934_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(906, NULL, NULL, NULL, '4', '4', '0', '27/02/2013 00:00 ', 'TO', 'Bird Food', '<p>\r\n <span style="color: rgb(51, 51, 51); line-height: 16px; font-family: verdana, arial, helvetica-normal, serif; font-size: 14px; font-weight: normal;">Bird Food</span></p>\r\n', NULL, NULL, 14.50, 18, 3, 17, 1, 'aust_post', 0.00, ' Regular Parcel - $25.20 Express Post - $78.90', 1, 'Description', NULL, NULL, NULL, 44.00, NULL, 0.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/item906_1.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL),
(1045, NULL, NULL, NULL, '0', '0', '0', NULL, NULL, 'testing', NULL, NULL, NULL, 6.50, 30, 3, 5, 1, 'aust_post', 0.00, ' Regular Parcel - $17.60 Express Post - $43.70', NULL, 'Description', NULL, NULL, NULL, 44.00, NULL, 32.00, NULL, NULL, NULL, '', 'N', NULL, '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', '/uploads/nopic.jpg', NULL);
-- --------------------------------------------------------ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER