Duplicating records using a sql query

**htown** · Dec 27, 2010 11:37

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

**r937** · Dec 27, 2010 13:05

Originally Posted by htown

... I am looking for the exact code i would need to put into the sql query box in phpMySQL

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

**htown** · Dec 27, 2010 13:16

I get a error. (See image)

**htown** · Dec 27, 2010 13:18

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

Code:

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.'

**donboe** · Dec 27, 2010 13:22

The command should be

Code MySQL:

show create table table_name;

**r937** · Dec 27, 2010 13:42

Originally Posted by htown

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

that's awesome

and which rows did you want duplicated?

**htown** · Dec 27, 2010 14:01

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?

**r937** · Dec 27, 2010 14:11

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

Code:

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 
     , 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

**htown** · Dec 27, 2010 14:38

I get this error: (See attachment)

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

Code:

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

**SpacePhoenix** · Dec 27, 2010 14:52

Originally Posted by htown

I get this error: (See attachment)

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

Code:

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

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

**r937** · Dec 27, 2010 14:59

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

**htown** · Dec 27, 2010 15:15

I have entered it this way:

Code:

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.

**r937** · Dec 27, 2010 15:27

Code:

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

User Tag List

Thread: Duplicating records using a sql query

Thread Tools

Display

Duplicating records using a sql query

Bookmarks

Bookmarks

Posting Permissions