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

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

    How to examine an UPDATE query before execution?


    Hello;

    Is there any ways to test UPDATE queries before running them? Something like EXPLAIN. I will have to run many updates on 10+ million rows tables, dont want to get into trouble. For example
    Code:
    update products_table SET status = (SELECT Floor(1 + ( Rand() * 4 )))
    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Is there any ways to test UPDATE queries before running them?
    test them on a smaller copy of the table (i.e. fewer rows)
    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,672
    Rep Power
    171
    Originally Posted by r937
    test them on a smaller copy of the table (i.e. fewer rows)
    I thought that woud be a good idea but didn't really work for me. Remember you helped me with this
    Code:
    INSERT INTO products_and_status 
                (product_id, 
                 status_id) 
    SELECT id, 
           (SELECT Floor(1 + ( Rand() * 4 ))) 
    FROM   products_table
    Took 10 seconds on a table with 1000 rows but for the 10+ million records table took 3 days and never stopped.

IMN logo majestic logo threadwatch logo seochat tools logo