#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171

    SELECT INSERT multiple columns, multiple rows


    Sweet and easy right?
    Code:
    INSERT INTO products_and_status
                (product_id)
    SELECT id
    FROM   `products_table`
    WHERE  id < 1000
    And now I need to add another columns there as well, so each of these rows have value 1 for the status_id. I need to change it a bit so this works:
    Code:
    INSERT INTO products_and_status
                (product_id, status_id)
    (
    SELECT id
    FROM   `products_table`
    WHERE  id < 1000  ) , 1
    Please help me with the synthax.

    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Code:
    INSERT 
      INTO products_and_status
         ( product_id
         , status_id )
    SELECT id
         , 1
      FROM products_table
     WHERE id < 1000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by r937
    Code:
    INSERT 
      INTO products_and_status
         ( product_id
         , status_id )
    SELECT id
         , 1
      FROM products_table
     WHERE id < 1000
    Excellent! Worked really fast too. Took 10 seconds to update a 7 million records table!

    As I don't have access to EXPLAIN for such queries, how can I test if this query is reasonably fast before running it? I want to somehow test if doing math functions like this is a good idea or not.
    Code:
    INSERT INTO products_and_status 
                (product_id, 
                 status_id) 
    SELECT id, 
           (SELECT Floor(1 + ( Rand() * 4 ))) 
    FROM   products_table
    Great thank you.

IMN logo majestic logo threadwatch logo seochat tools logo