How to test an SQL Update statement before running it?

How to test an SQL Update statement before running it?

Asked on October 26, 2018 in Database.
Add Comment


  • 8 Answer(s)

    check rows are affected by running a select using the same WHERE clause as the UPDATE.

    So if we UPDATE is

    UPDATE foo
        SET bar = 42
    WHERE col1 = 1
        AND col2 = 'foobar';
    

    To find which rows will be updated:

    SELECT *
    FROM foo
    WHERE col1 = 1
        AND col2 = 'foobar';
    
    Answered on October 26, 2018.
    Add Comment

    MySQL

    set autocommit=0;
    

    It sets the autommit off for the current session.

    Answered on October 26, 2018.
    Add Comment

    This code can be used to test an SQL Update statement before running it

    START TRANSACTION;
    SELECT * FROM nicetable WHERE somthing=1;
    UPDATE nicetable SET nicefield='VALUE' WHERE somthing=1;
    SELECT * FROM nicetable WHERE somthing=1; #check
     
    COMMIT;
    # or if you want to reset changes
    ROLLBACK;
     
    SELECT * FROM nicetable WHERE somthing=1; #should be the old value
    

    The above code can be used to test an SQL Update statement before running it

    Answered on October 26, 2018.
    Add Comment

    If you have an update statement like:

    UPDATE 
    wp_history
    SET history_by="admin"
    WHERE
    history_ip LIKE '123%'

    You hash UPDATE and SET out for testing, then hash them back in:

    SELECT * FROM
    #UPDATE
    wp_history
    #SET history_by="admin"
    WHERE
    history_ip LIKE '123%'

    It works for simple statements.

    An additional practically mandatory solution is, to get a copy (backup duplicate), whenever using update on a production table. Phpmyadmin > operations > copy: table_yearmonthday. It just takes a few seconds for tables <=100M.

    Answered on January 14, 2019.
    Add Comment

    Now, you may be thinking “Won’t that be a lot of work to re-write my query each time?” No, not really. It’s actually pretty simple. Watch:

    -- DELETE version
    DELETE 
    FROM [Sales].[Store]
    WHERE [Store].[BusinessEntityID] NOT IN 
    		(SELECT BusinessEntityId
    		 FROM [Person].[BusinessEntityContact])
    -- UPDATE version
    UPDATE [Sales].[Store] SET BusinessEntityID = NULL
    WHERE [Store].[BusinessEntityID] NOT IN 
    		(SELECT BusinessEntityId
    		 FROM [Person].[BusinessEntityContact])
    -- SELECT version
    SELECT * 
    FROM [Sales].[Store]
    WHERE [Store].[BusinessEntityID] NOT IN 
    		(SELECT BusinessEntityId
    		 FROM [Person].[BusinessEntityContact])
    Answered on January 14, 2019.
    Add Comment

    This isn’t so much of a best practice, it comes more under the heading of being careful. Let’s say you have a DELETE statement (or UPDATE) that you need to run. You can certainly put it in a transaction. Run it, test it, then if you’ve made a mistake roll it back. Of course lets say the DELETE takes half an hour, and then another hour to roll back. You have now just lost an hour and a half. Instead why don’t you convert your query to a SELECT, maybe start with a COUNT(1) to check that the number of rows is at least in the right ballpark, then a TOP 100 or so to make sure you are hitting the right data, etc.

    Answered on January 14, 2019.
    Add Comment

    Please take a look at the following code that I’ve written to update WHMCS prices:

    # UPDATE tblinvoiceitems AS ii
    
    SELECT                        ###  JUST
        ii.amount AS old_value,   ###  FOR
        h.amount AS new_value     ###  TESTING
    FROM tblinvoiceitems AS ii    ###  PURPOSES.
    
    JOIN tblhosting AS h ON ii.relid = h.id
    JOIN tblinvoices AS i ON ii.invoiceid = i.id
    
    WHERE ii.amount <> h.amount   ### Show only updatable rows
    
    # SET ii.amount = h.amount

    This way we clearly compare already existing values versus new values.

    Answered on January 16, 2019.
    Add Comment
    
    The UPDATE statement is used to change data within columns of a SQL Server data table.  In this article we’ll explore how to use the UPDATE statement.  We discuss some best practices, limitations, and wrap-up with several examples.
    This is the third article in a series of articles.  You can start at the beginning by reading Introduction to SQL Server Data Modification Statements.
    
    WITH topSalesPerson (FullName, SalesLastYear, City, rowguid) AS ( SELECT S.FirstName + ' ' + S.LastName, 
           S.SalesLastYear,
           S.City ,NEWID() FROM   Sales.vSalesPerson S WHERE  S.SalesLastYear > 1000000 ) INSERT INTO esqlSalesPerson (FullName, SalesLastYear, City, rowguid) SELECT FullName,
           SalesLastYear,
           City,
           rowguid FROM   topSalesPerson
    Answered on January 16, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.