如何在运行SQL Update语句之前测试它？ [英] How to test an SQL Update statement before running it?

问题描述

在某些情况下，在生产中运行UPDATE语句可以节省日期。但是，borked更新可能比最初的问题更糟。

缺少使用测试数据库，什么是选项来告诉更新语句在运行之前会做什么？ / p>

解决方案

除了使用Imad已经说过的事务（这应该是强制性的），你还可以做一个健全检查哪些行

因此，如果您的UPDATE是

<$ p，则会影响到使用与UPDATE相同的WHERE子句的select。 $ p> UPDATE foo
SET bar = 42
WHERE col1 = 1
AND col2 ='foobar';


以下将显示将更新哪些行：

  SELECT * 
 FROM foo 
 WHERE col1 = 1 
 AND col2 ='foobar'; 
  

In some cases, running an UPDATE statement in production can save the day. However a borked update can be worse than the initial problem.

Short of using a test database, what are options to tell what an update statement will do before running it?

解决方案

In addition to using a transaction as Imad has said (which should be mandatory anyway) you can also do a sanity check which rows are affected by running a select using the same WHERE clause as the UPDATE.

So if you UPDATE is

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

The following will show you which rows will be updated:

SELECT *
FROM foo
WHERE col1 = 1
  AND col2 = 'foobar';

这篇关于如何在运行SQL Update语句之前测试它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！