SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru htown's Avatar
    Join Date
    Mar 2005
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Duplicating records using a sql query

    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
    Houston Brown
    Split Light Designs
    Bringing your ideas to light.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by htown View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru htown's Avatar
    Join Date
    Mar 2005
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get a error. (See image)
    Attached Images Attached Images
    Houston Brown
    Split Light Designs
    Bringing your ideas to light.

  4. #4
    SitePoint Guru htown's Avatar
    Join Date
    Mar 2005
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.'
    Houston Brown
    Split Light Designs
    Bringing your ideas to light.

  5. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The command should be
    Code MySQL:
    show create table table_name;

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by htown View Post
    I am not sure but perhaps this is what you are needing.
    that's awesome

    and which rows did you want duplicated?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru htown's Avatar
    Join Date
    Mar 2005
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Houston Brown
    Split Light Designs
    Bringing your ideas to light.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru htown's Avatar
    Join Date
    Mar 2005
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Attached Images Attached Images
    Houston Brown
    Split Light Designs
    Bringing your ideas to light.

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,027
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by htown View Post
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru htown's Avatar
    Join Date
    Mar 2005
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Houston Brown
    Split Light Designs
    Bringing your ideas to light.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •