UPDATE, CASE results

Transact-SQL
1

Hey, everyone. I'm needing to update the values in a column in my table when certain conditions are met. My problem is, it's telling me that 2,558,000 rows are affected (which is every row in my table). Now, I'm not the smartest when it comes to SQL, but I'm going to assume that the "else" part of the case statement is what's causing every other row to be affected (even though no data is actually being changed. Am I correct in that?

Update Manual.dbo.stu_award_transactions_BU071515  
set scheduled_amount = 
	case when t.poe_token = 80                         
			and t.scheduled_amount > 0
		 then 1851
		 when t.poe_token = 81                         
			and t.scheduled_amount > 0
		 then 1851
		 when t.poe_token = 82                        
			and t.scheduled_amount > 0
		 then 1851
		 else t.scheduled_amount end
from stu_award_year a
join stu_award sa on a.stu_award_year_token = sa.stu_award_year_token
join stu_award_transactions t on sa.stu_award_token = t.stu_award_token
where a.award_year_token = 2015 and fund_ay_token = 17313

Additionally, how can I tell it to show me the rows which were actually changed based on the where statements?

2

To see what rows are affected , change the update into a select

 /*Update Manual.dbo.stu_award_transactions_BU071515  
    set scheduled_amount = 
    case when t.poe_token = 80                         
            and t.scheduled_amount > 0
         then 1851
         when t.poe_token = 81                         
            and t.scheduled_amount > 0
         then 1851
         when t.poe_token = 82                        
            and t.scheduled_amount > 0
         then 1851
         else t.scheduled_amount end*/
select *
from stu_award_year a
join stu_award sa on a.stu_award_year_token = sa.stu_award_year_token
join stu_award_transactions t on sa.stu_award_token = t.stu_award_token
where a.award_year_token = 2015 and fund_ay_token = 17313

Also you can use the Output clause of the UPDATE(see BooksOnLine)

3

But that won't actually show me the things to be changed since that select statement doesn't take into account the "poe token" cases.

4

add:

AND t.poe_token IN (80, 81, 82)
AND t.scheduled_amount > 0

which will restrict the update to only those rows that the CASE statement is interested in

(Good practice anyway as updating a row with actually, materially, changing the data in any columns just clogs up the LOG (and probably gives SQL a hard time shuffling stuff around in some circumstances. It will also cause any UPDATE trigger to fire - that might be benign, or it might waste time & space, for example, creating an Audit Table row(s))

1 Like
5

You didn't include the table being UPDATEd in the joins, thus all rows are being affected. SQL Server syntax makes it very easy to add that table to the joins. I had to guess on specific column on which to join.

Also, you should add the alias in front of column "fund_ay_token" so that it's clear which table it comes from:

Update t_bu
set scheduled_amount = 
	case when t.poe_token = 80                         
			and t.scheduled_amount > 0
		 then 1851
		 when t.poe_token = 81                         
			and t.scheduled_amount > 0
		 then 1851
		 when t.poe_token = 82                        
			and t.scheduled_amount > 0
		 then 1851
		 else t.scheduled_amount end
from stu_award_year a
inner join stu_award sa on a.stu_award_year_token = sa.stu_award_year_token
inner join stu_award_transactions t on sa.stu_award_token = t.stu_award_token
inner join Manual.dbo.stu_award_transactions_BU071515 t_bu on sa.stu_award_token = t_bu.stu_award_token
where a.award_year_token = 2015 and /*??.*/fund_ay_token = 17313
1 Like
6

I missed that, well spotted.

We ONLY EVER use Update syntax with an ALIAS for the update table (and always "U" for consistency):

UPDATE U
SET MyCol1 = XXX, MyCol2 = YYY, ...
-- SELECT MyCol1, MyCol2, ...
FROM MyTable1 AS U
     JOIN MyTable2 AS X
         ON X.ID = U.ID
    ...

which avoids any issue of mis-naming / positioning tables

Also?? handy as you can put a

-- SELECT ...

in front of the FROM statement allowing easy debugging of what will be selected for the UPDATE

1 Like
7

Kristen, and Scott:

Thank you both for your input on this. What was really getting me is that the script has been run once yearly for the last 6 years and have never had a problem. Then we run it yesterday and it loses its mind. This was my first time to run the script, and I feel there might have been some changes made to it that I wasn't privy to, as I've just recently taken over this post.

Also, FWIW -- (funny, but scary at the same time).... I filled up our transaction log yesterday trying to run this script over, and over, and over again... OOPS!

8

For testing code (even if I think its only doing SELECT - e.g. an EXEC SProc that I think only does SELECT) I always surround it with a transaction

BEGIN TRANSACTION
    some code to test
-- COMMIT
-- ROLLBACK

Then I highlight either COMMIT or ROLLBACK and execute that.

Probably won't help you with the log-full issue (although it might??) as the Log will still be full of all the junk that got rolled back. Any "mess" in the DB won't be there though, which could save you RESTORE

More normally I do this:

BEGIN TRANSACTION
    some code to test
-- COMMIT
ROLLBACK

so that the code automatically rolls back if I just execute it, but I can then highlight down-to, but not including, the ROLLBACK and run that. I would be doing that assuming that it will work! but that still gives me the chance to highlight ROLLBACK if it gives me way more rowcount than I am expecting! and if it looks OK then I can highlight COMMIT and execute that.

Typically my scenrio is more complex than the example I have shown, so perhaps something more like:

BEGIN TRANSACTION
UPDATE U
SET SomeColumn =XXX
FROM MyTable
WHERE SomeOtherColumn = YYY

SELECT Column1, Column2
FROM MyTable
WHERE SomeCondition

-- COMMIT
ROLLBACK

so that I get some feedback on the relevant data to see what's what ... and then decide to Commit / Rollback

By the by ... if your Log is filling up my best guess would be that you are not backing it up often enough. I always question anywhere that backs up the log LESS that every 10 minutes. The trend seems to be to move to every minute or two. You still need enough Log BACKUP Disk space ... but any strain on the Log file itself would go away.

This will tell you the average log backup interval for a database

-- Number of Log Backups per day
SELECT	[Date] = DATEADD(Day, DATEDIFF(Day, 0, BS.backup_start_date), 0),
	[Type] = CASE BS.type WHEN 'D' THEN 'Full'
				WHEN 'I' THEN 'Differential'
				WHEN 'L' THEN 'Log'
				WHEN 'V' THEN 'VerifyOnly'
				ELSE '[' + BS.type + ']'
				END,
	[Count] = COUNT(*),
	[Interval(mins)] = (24 * 60) / NullIf(COUNT(*), 0),	-- 24 hours x 60 minutes in a day :)
	BS.database_name
FROM	msdb.dbo.backupset AS BS
WHERE	1=1
	AND BS.database_name = DB_Name()	-- Current database
	AND BS.backup_start_date > DATEADD(Day, DATEDIFF(Day, 0, GetDate())-10, 0)	-- Limit to last 10 days
	AND BS.backup_start_date < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)		-- Exclude today (partial data)
	AND BS.type = 'L'	-- Log backups only
GROUP BY DATEADD(Day, DATEDIFF(Day, 0, BS.backup_start_date), 0), BS.type, BS.database_name
ORDER BY BS.database_name, [Date], type