Duplicating records using a sql query

Databases
1

I have a situation where I need to insert (duplicate) 50 different products in an e-commerce site and it seems to make more sense doing it this way than doing through the Virtuemart interface. I have a client that wants to show 50 stone sizes and instead of just putting in all the stones in once and using an option to choose one of the four sizes to order, he wants to show 50 stones in a 7mm category, 50 stones in a 8mm category, 50 stones in a 10mm category, 50 stones in a 12mm category. I advised against it but he is insistent. So now since I do not want to enter 150 stones (I have already entered the 50) back into the database using the VM admin, I figured there is some way to insert a copy each of the stones back into the database using a query. So basically there will be four of each kind and then I will go back and add 7mm, 8mm, 10mm, and 12mm to the end of the names to show the difference.

I am not a database guy and my sql knowledge is very limited. Perhaps I am asking too much her ebut I am looking for the exact code i would need to put into the sql query box in phpMySQL

Cheers,
Houston

2

then you will have to give us the exact table definition – do a SHOW CREATE TABLE, please

3

I get a error. (See image)

4

I am not sure but perhaps this is what you are needing.

CREATE TABLE `jos_vm_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) NOT NULL DEFAULT '0',
  `product_parent_id` int(11) NOT NULL DEFAULT '0',
  `product_sku` varchar(64) NOT NULL DEFAULT '',
  `product_s_desc` varchar(255) DEFAULT NULL,
  `product_desc` text,
  `product_thumb_image` varchar(255) DEFAULT NULL,
  `product_full_image` varchar(255) DEFAULT NULL,
  `product_publish` char(1) DEFAULT NULL,
  `product_weight` decimal(10,4) DEFAULT NULL,
  `product_weight_uom` varchar(32) DEFAULT 'pounds.',
  `product_length` decimal(10,4) DEFAULT NULL,
  `product_width` decimal(10,4) DEFAULT NULL,
  `product_height` decimal(10,4) DEFAULT NULL,
  `product_lwh_uom` varchar(32) DEFAULT 'inches',
  `product_url` varchar(255) DEFAULT NULL,
  `product_in_stock` int(11) NOT NULL DEFAULT '0',
  `product_available_date` int(11) DEFAULT NULL,
  `product_availability` varchar(56) NOT NULL DEFAULT '',
  `product_special` char(1) DEFAULT NULL,
  `product_discount_id` int(11) DEFAULT NULL,
  `ship_code_id` int(11) DEFAULT NULL,
  `cdate` int(11) DEFAULT NULL,
  `mdate` int(11) DEFAULT NULL,
  `product_name` varchar(64) DEFAULT NULL,
  `product_sales` int(11) NOT NULL DEFAULT '0',
  `attribute` text,
  `custom_attribute` text NOT NULL,
  `product_tax_id` int(11) DEFAULT NULL,
  `product_unit` varchar(32) DEFAULT NULL,
  `product_packaging` int(11) DEFAULT NULL,
  `child_options` varchar(45) DEFAULT NULL,
  `quantity_options` varchar(45) DEFAULT NULL,
  `child_option_ids` varchar(45) DEFAULT NULL,
  `product_order_levels` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  KEY `idx_product_vendor_id` (`vendor_id`),
  KEY `idx_product_product_parent_id` (`product_parent_id`),
  KEY `idx_product_sku` (`product_sku`),
  KEY `idx_product_ship_code_id` (`ship_code_id`),
  KEY `idx_product_name` (`product_name`)
) ENGINE=MyISAM AUTO_INCREMENT=164 DEFAULT CHARSET=utf8 COMMENT='All products are stored here.'
5

The command should be

show create table table_name;
6

that’s awesome

and which rows did you want duplicated?

7

Do those not represent columns in a table? So that each product has each of these columns? What I need is all 50 stones to be duplicated 3 times in the database. Then I will need the numbers 8, 10,12 appended to each of the new (duplicated) products_sku field. Does this make sense?

8

so you have only 50 rows and you want them all duplicated?

so then you don’t need a WHERE clause, at least not the first time you do this

INSERT
  INTO jos_vm_product 
     ( -- note product_id is not listed
     , vendor_id
     , product_parent_id 
     , product_sku 
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels )
SELECT vendor_id
     , product_parent_id 
     , [COLOR="Blue"]CONCAT(product_sku,'8')[/COLOR]
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels
  FROM jos_vm_product

:slight_smile:

9

I get this error: (See attachment)

Here is the code I am using ( I just removed you comment)

INSERT
  INTO jos_vm_product 
     , vendor_id
     , product_parent_id 
     , product_sku 
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels )
SELECT vendor_id
     , product_parent_id 
     , CONCAT(product_sku,'8')
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels
  FROM jos_vm_product
10

You’re missing the ( at the start of the list of field names

11

the “near” hint in the error message usually (as in this case) immediately pinpoints where the error was detected

i’m sorry for the typo, i outsmarted myself with that comment, and left a leading comma which should have been removed

12

I have entered it this way:

INSERT
  INTO jos_vm_product 
     (
     , vendor_id
     , product_parent_id 
     , product_sku 
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels )
SELECT vendor_id
     , product_parent_id 
     , CONCAT(product_sku,'8')
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels
)
  FROM jos_vm_product

And I entered it as you sent it and the way you sent it without the “-- note product_id is not listed” and I still get an error.

I am not sure what I am doing wrong. Sorry.

13 
INSERT
  INTO jos_vm_product 
     ( vendor_id
     , product_parent_id 
     , product_sku 
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels )
SELECT vendor_id
     , product_parent_id 
     , CONCAT(product_sku,'8')
     , product_s_desc 
     , product_desc 
     , product_thumb_image 
     , product_full_image 
     , product_publish 
     , product_weight 
     , product_weight_uom 
     , product_length 
     , product_width 
     , product_height
     , product_lwh_uom
     , product_url 
     , product_in_stock 
     , product_available_date 
     , product_availability 
     , product_special 
     , product_discount_id 
     , ship_code_id 
     , cdate 
     , mdate 
     , product_name 
     , product_sales
     , attribute 
     , custom_attribute 
     , product_tax_id 
     , product_unit 
     , product_packaging 
     , child_options 
     , quantity_options 
     , child_option_ids 
     , product_order_levels
  FROM jos_vm_product

sorry