Make preview of changing rows affect by TSQL Update statment
-
Hello
I like to know if it´s possible in QA make a preview of a UPDATE TSQL command in QA grid to show all the records affects by them, without making the commit transaction, for example in ACCESS it´s possible.
Many thanks
Luis Santos
-
Hi Luis,
I'm not certain on what you mean, but you could run the update statement with a begin tran and leave out the commit tran.
Like this:
begin tran
update table
set...
where...
You can then see what your changes look like and run a commit tran if you're happy. Or a rollback tran if you're not.
Just be careful to make sure you do something with the transaction. You don't want to leave an open transaction and then go home, fogetting about it.
-
Hello Karl
I want to see on the QA grid What records i affect with this UPDATE without making the COMMIT TRANSACTION, in ACCESS after building in design view the query , i can see the rows before make running the Update query.
I don´t understand what you means with:
"Just be careful to make sure you do something with the transaction. You don't want to leave an open transaction and then go home, fogetting about it."
I think after seeing the result , if it´s not the pretending result i make do :
begin tran
update table
set...
where...
Rollback Transaction
Many thanks for your helps
Luis Santos
-
Luis
If your update query looks like this:
UPDATE MyTable
SET MyCol = 'NewValue'
WHERE SomeOtherColumn = 'SomeOtherValue'
Then you can see what rows you are affecting by running this:
SELECT * FROM MyTable
WHERE SomeOtherColumn = 'SomeOtherValue'
John
-
I would get in the habit of always using a FROM clause in your updates. then for any UPDATE statement, you can just replace the first part to convert it into the equivalent SELECT. For example, to refer to the previous post:
- UPDATE t
- SET MyCol = 'NewValue' , AnotherCol = 567.32
- FROM MyTable t
WHERE SomeOtherColumn = 'SomeOtherValue'
becomes something like:
- SELECT t.*,
- t.MyCol, 'NewValue', t.AnotherCol, 567.32
- --UPDATE t
- --SET MyCol = 'NewValue' , AnotherCol = 567.32
- FROM MyTable t
WHERE SomeOtherColumn = 'SomeOtherValue'
The idea is this keeps your FROM clause intact, so you can be sure that the record you've looked at are the same ones you will update when you switch the code back.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Tim has the right idea but I take it one step further.... I always write the SELECT first. When I'm happy with what I'm seeing, THEN I convert it to an update.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Idea is absolutely right and proven by practice.
But there is one important note for newbies:
You UPDATE ALIAS and always only alias, don't repeat the table name twice.
Do exactly as it's done in Tim's solution. Don't miss details.
_____________
Code for TallyGenerator -
>>You UPDATE ALIAS and always only alias, don't repeat the table name twice.
What's the reason for that? Never had a problem with naming the table twice... and makes lengthy code much easier to read because you don't need to view the FROM clause to figure out which table you're updating...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
(Jeff-) I agree re writing the SELECT first.
re using full names for aliased tables, I'm sure I remember that in a previous version you could not reference the base table name in the SET clause if it was aliased in the FROM clause. I don't think it was just 6.5, maybe 7 as well? But you certainly can do it in 9 and I think 8.
However, I agree that you shouldn't do it. I think it's a good idea always to use table aliases whenever possible - so the full name occurs only once, in the FROM clause. Using a consistent and readable aliasing scheme (e.g. initial letters of table name + an index number if for multiple instances) means that it is easier to read the code since it isn't cluttered with long, sometimes unreadable, table names (schema-qualified of course, e.g. [sales_productionR4_FG2].[R4ALLOCS_88__7653700f]).
But a particular reason always to use table aliases in the SET clause: if you reference the same base table twice in your FROM clause, the parser can't disambiguate the table name unless you use the specified aliases. Since your aim shouid be to rigorously drill good habits like this (so that when someone says - did you update all the rows in the customers table? You can confidently say no because you remember doing the ritual), leaving you to worry about the interesting stuff, not procedure. Who says learnt habit is the enemy of freedom?
BTW, I may as well mention explicitly that this approach (i.e. read first, then write - notice any similarity to standard OLTP application design?) should also be used for DELETE statements:
delete a --------------------delete code - comment out to run check
--select a.* ----------------checking code - comment out to delete
from any_table at
join any_other_table aot
on aot.at_id = at.id
where aot.somecolumn = 'a value'
But table aliasing doesn't really apply to set-based insert statements which already involve a grammatical SELECT statement (to generate values for new rows), and never have existing target rows to worry about. But slightly more complicatedly and bearing in mind that you should always use a <target column list> in your insert statements, you can check for column compatibility between source and target by issuing:
--insert atable -------insert code
--(col1,col2,col3)-----comment out to run check
select top 1 col1,col2,col3 -------checking code
from atable where 1=0 union all----comment out to insert
select cola,colb,null
from anothertable ant
where ant.id = 345
If the SELECT succeeds, it should show you the new records and confirm that you have the right number of columns of the right data types in the right order.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Well done... but, all that being said, the following works and there is no ambiguity that is not resolved by the optimizer... (maybe I just misunderstood what Seqiy posted and if so, I appologize)...
- UPDATE MyTable
- SET MyCol = 'NewValue' , AnotherCol = 567.32
- FROM MyTable t
WHERE SomeOtherColumn = 'SomeOtherValue'
Of course, I wouldn't write it that way because there are no joined tables... this is how I'd write the example above (FROM clause not required)...
- UPDATE MyTable
- SET MyCol = 'NewValue' , AnotherCol = 567.32
- WHERE SomeOtherColumn = 'SomeOtherValue'
Only when you join to another table do you need to institute the FROM clause of an update...
UPDATE Table1
SET somecola = t2.someval
FROM Table1 t1,
Table2 t2
WHERE t1.somecolb = t2.somecolb
The above example will not suffer any ambiguity problems either.
Now, if you want to do this...
UPDATE t1
SET somecola = t2.someval
FROM Table1 t1,
Table2 t2
WHERE t1.somecolb = t2.somecolb
...it'll still work without any ambiguity. If that's your preference, go for it...
...But, the reason the DBA's I work with and I don't allow it is because if you want to search for all stored procedures that update a particular table, you may not find it because not everyone will use the same alias for the table.
My point is that the statement about ALWAYS using an alias on the UPDATE in the presence of a FROM clause is simply incorrect. In fact, although it certainly seems to work with a table alias, BOL says it must be a table name, not a <table_source>...
UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]
{ { [ FROM { < table_source > } [ ,...n ] ]
[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,...n ] ) ]
< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
| view_name [ [ AS ] table_alias ]
| rowset_function [ [ AS ] table_alias ]
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| < joined_table >
What WILL cause the occasional (doesn't always happen) but very serious ambiguity, is something like the following...
UPDATE Table1
SET somecola = t2.somecolc
FROM Table2 t2
WHERE Table1.somecolb = t2.somecolb
...I've witnessed these types of improper updates (target of update not included in the from clause and a join is present) slamming a 4 CPU box into the wall for two solid hours... we got kinda lucky because the same query always took that long so we knew it wasn't cache because when correctly rewritten, the update took less than two minutes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I agree that for trivial UPDATEs - those involving just one table instance, you needn't use a FROM clause - except that if you do the SELECT first, you will already have a FROM clause ready - representing exactly the records you have just checked.
re: ambiguity problems. Try doing this:
- UPDATE Table1
SET somecola = t2.someval
FROM Table1 t1
JOIN Table1 t2
ON t1.
somecolb = t2.somecolb - WHERE t1.somecolc < 5 AND t2.somecolc = 5
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant - UPDATE Table1
-
Have you got any test data setup that you might want to share or do I have to do this the hard way?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok Tim... hang on... gott wipe some egg off my face
I gotta admit, for self join updates, the Update <alias> is the way to go because it really keeps the code simple compared to a dervied table method.
I think the way I can preserve the "search" I was talking about before is to alias the target table with it's own name... this works (if not a bit ugly)...
UPDATE Table1
SET somecola = t2.someVal
FROM Table1 AS Table1
JOIN Table1 t2
ON Table1.somecolb = t2.somecolb
WHERE Table1.somecolc < 5 AND t2.somecolc = 5
...and doesn't complicate the search (which has really come in handy) any more than having to search for the table name with and without "dbo.".
If the kind of search I'm talking about isn't important, then alias everything as Serqiy suggested... at least, now, I know why he suggested it. Thanks Tim.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply