How to test an SQL Update statement before running it?
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';
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
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.
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])
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.
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.
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)
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