I have a log table where accidentally one date column got updated with current date for 9 Million records.

Luckily I had a backup table, so now I want to populate data from backup table to current table for the only date column.

Update [table1]
Set EndTime = t2.EndTime 
FROM [table1] t1 
Inner Join table2as t2
On t1.ProcessExecutionID = t2.ProcessExecutionID

I tried the above query but it is updating wrong values to the column.

enter image description here

  • Are there any update triggers on Table1? – Scott Hodgin Aug 24 '18 at 10:53
Update [table1] t1
Set EndTime = (select t2.EndTime FROM table2 as t2
where t1.ProcessExecutionID = t2.ProcessExecutionID)

But I wonder if 8M updates will fit in a single transaction. If not, split them up.

  • I am getting the following error:Msg 512, Level 16, State 1, Line 24 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. – l.lijith Aug 24 '18 at 10:12
  • Do all records for a given processexecutionid have the same endtime? – Gerard H. Pille Aug 24 '18 at 10:13
  • No...all the processexecutionid have different endtime. – l.lijith Aug 24 '18 at 10:14
  • That is not an answer to my question. What is the primary key of table1? – Gerard H. Pille Aug 24 '18 at 10:15
  • 1
    No it isn't: "Subquery returned more than 1 value"; Try this "select ProcessExecutionID, count( * ) from table2 group by ProcessExecutionID having count( * ) > 1". – Gerard H. Pille Aug 24 '18 at 11:16

That’s awesome on the backup. Question: how many of the rows in the backup table are current with the altered table?

Perhaps you should consider inserts and updates on the backup table instead.

If not:

1) check for any constraints on the table. Also be wise and see what indices are used for the operation.

  • a covering index is significantly less expensive than lookups or a full Clustered index scan.

2) Can you tolerate downtime for the whole restore?

  • Or can you temporarily build an index, insert with this index, remove said index, and run stats on the table...and end with less impact?

3) Check the column data length and estimate the log size required for the operation.

  • SELECT MAX(DATALENGTH(col)) / 1024.0/ 1024.0 AS SpaceMB FROM TABLE

4) You may need to split the transaction up.

(Using either a cursor or a number for the table possibly through ID name)


Hint: the error you get from your sub query is because the join is wrong. Better to find what columns make a unique row and update on that. In a way, be glad this rule on subqueries is enforced as you may have ended with dirty data. 😅

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.