Damaged database table with invalid query: what did it do and roll back?

Question

I made a mistake when starting up what should have been a quick update against my fact table (lines 200M) as follows:

update dbo.primary_fact set count_of_loan_obligors = on from dbo.staging_fact f -- notice that this is not the same table as the one in the update clause inner join ##Obligor_Count o on (f.time_dimension_id = o.time_dimension_id and f.account_dimension_id = o.account_dimension_id) 

Must be:

  from dbo.primary_fact f 

A properly formed update similar to this one (1 day, 87 thousand accounts) usually ends in a minute or 2. After starting for 12 minutes, I wondered what had been taking so long and noticed my mistake.

I canceled the query in SQL Server Management Studio, which, as I understand it, will drop all the terrible ones that I called (can someone confirm?)

But my big question is: what does a malformed request do?

---

Update: The cancellation action is completed, an hour and 39 minutes later. Database administrators were too slow to kill - just as good.

Correctly formed update completed in 8 seconds.

Second update: Failed to set values ​​from the original (erroneous) update after successful cancellation of the order in SSMS. I would interpret this as meaning that any pending updates were thrown back.

Answer 1

But my big question is: what is a malformed request?

It will update dbo.primary_fact.count_of_loan_obligors with the same value for all rows. The value will have a value from ##Obligor_Count.n . It is hard to understand what meaning would be.

Here is a small test that basically does what you did:

 declare @T1 table (ID int) declare @T2 table (ID int) insert into @T1 values (0) insert into @T1 values (0) insert into @T2 values (2) insert into @T2 values (1) update @T1 set ID = T2.ID from @T2 as T2 select * from @T1 

Result:

 ID 2 2 

In this case, @T1 updated with the first line in @ @T2 .

Answer 2

Well, first of all, it looks like you might just re-run your query using the rule from the sentence instead of the staging_fact table, and it will overwrite any booboos you made. This is good news and the joy of working with fact tables.

The bad news is that in my experience, SSMS doesn't roll back anything unless you actually run it in a transaction, so your data is probably a big bucket with an error right now.

I hope you enjoy the last 12 minutes of your life, because you are going to enjoy it again.

Answer 3

I'm worried that neither version does anything good, because they both do not have a WHERE clause connecting the updated table to the source of the new values ​​(f internal join o). I expect the WHERE primary_fact.time_dimension_id=f.time_dimension_id AND primary_fact.account_dimension_id=f.account_dimension_id line WHERE primary_fact.time_dimension_id=f.time_dimension_id AND primary_fact.account_dimension_id=f.account_dimension_id be deleted in the copy / paste.

As long as the reference tables have columns of these names, joining f and o will do just fine. Then these values ​​will be used to update primary_fact, either with the WHERE clause, or somehow I don't know. The UPDATE / FROM syntax is not standard SQL, but it is widely supported. Maybe SQL SERVER even adds a WHERE clause to the default. Postgresql does not.