Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Query

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-09-08 : 12:03:43

I normally work in Access linking to views in SQL Server 2000 so I can write reports and run queries. We recently went to SQL Server 2005 and I have not spent a whole lot of time in it.

I need to update a single column in about 150 rows in a table that has 400,000+ rows. I created a table in SS 05 that has the 2 fields to join on the main table so I can display or update the the rows to change.

I'm paranoid when I'm working in the database tables where I can make mistakes and blow away large amounts of data. I normally work with views for reporting only. I simple need to change a 1 to 0 so I thought I would just use a select query to display the rows and change the values by hand. When I run the query I can not edit the field. Only when I open the table can I edit the field, but then I can't filter for my 150 rows.

I wrote an UPDATE query, but I would like to know how many rows it will effect before I run the query. I'm paranoid. I can't find a tool in SS 05 that will analyze the query and tell me how many rows will be affected before I run the query. In Access I always get a warning of how many rows will be effected and I can back up before I commit the updates.

Below is the query. How can I validate it and find out what it will do before it does it. DeletEncounters has the 150 rows with 2 fields.

UPDATE    tbl_encounters
SET       record_active = 0
FROM      tbl_encounters INNER JOIN
	DeletEncounters ON tbl_encounters.patient_id = DeletEncounters.PatID AND tbl_encounters.encounter_date = DeletEncounters.EncDate
WHERE   (tbl_encounters.record_active = 1)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-08 : 12:39:03

Use a transaction so you can rollback if the number of affected rows isn't ok.

BEGIN TRAN (hilight and run)

here comes your update statement... (highlight and run)
the update statement tells you the number of affected rows.

if num rows is ok then execute a
COMMIT (highlight and run)

if not then execute a
ROLLBACK (highlight and run)
(and all updated rows are rolled back to the old values)

But don't forget to do a ROLLBACK or a COMMIT!
The table will be locked until commit/rollback!

No, you're never too old to Yak'n'Roll if you're too young to die.

CSears
Starting Member

39 Posts

Posted - 2010-09-08 : 12:49:48

Another good way to double check what you are deleting is to take your where clause (Or FROM statement in this case) of your update and put a SELECT * in front of it instead of your UPDATE statement.


-- UPDATE    tbl_encounters
-- SET       record_active = 0
SELECT * 
FROM      tbl_encounters INNER JOIN
	DeletEncounters ON tbl_encounters.patient_id = DeletEncounters.PatID AND tbl_encounters.encounter_date = DeletEncounters.EncDate
WHERE   (tbl_encounters.record_active = 1)

This will return all the rows that will be affected by the update statement

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-09-09 : 11:18:26

Both good suggestions. What I ended up doing was restoring a fresh back up to a test database and running the query there first.

How do I "Highlight and run", though. In programming I do this sort of thing a lot to step through my code one line at a time so I can watch what is happening. In fact, I even used BEGINTRAN and COMMIT and ROLLBACK in my code as I step through it. I don't see any kind of debug tool like this in the 05 Management Studio.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources