I know this should be simple - update hacked table with good data from a copy

Question

I know this should be simple - update hacked table with good data from a copy

keith-872606

Grasshopper

Points: 24
More actions
September 7, 2008 at 10:37 am

#190543

Hi,
I wonder if anyone can help me
I have a database with over 6000 records. At some point it was hacked, but the hack wasn't noticed at first. This
left me with a situation where I have a lot of new records that have been added since the hack. They are all valid.
I'd like to restore all the records from my last backup, whilst keeping the new records intact.
To visualize this....
Contents of existing (hacked) Table : Test
Table : Test
ID Column1Content Column 2 Content
1Record 1 : HackedRecord1 : Hacked
2Record 2 : HackedRecord2 : Hacked
3Record 3 : HackedRecord3 : Hacked
4Record 4 : HackedRecord4 : Hacked
5Record 5 : HackedRecord5 : Hacked
6Record 6 : GoodRecord6 : Good
7Record 7 : GoodRecord7 : Good
What I've done is to restore the good records as a table called Test_2
Contents of Existing (Good) Table :Test_2)
Table : Test_2
ID Column1Content Column 2 Content
1Record 1 : GoodRecord1 : Good
2Record 2 : GoodRecord2 : Good
3Record 3 : GoodRecord3 : Good
4Record 4 : GoodRecord4 : Good
5Record 5 : GoodRecord5 : Good
And, obviously, I want to achieve this
Table : Test
ID Column1Content Column 2 Content
1Record 1 : GoodRecord1 : Good
2Record 2 : GoodRecord2 : Good
3Record 3 : GoodRecord3 : Good
4Record 4 : GoodRecord4 : Good
5Record 5 : GoodRecord5 : Good
6Record 6 : GoodRecord6 : Good
7Record 7 : GoodRecord7 : Good
All that needs to be done is to update all records in 'Test' that have a corresponding
ID in 'Test_2' with the values in 'Test_2'
Bear in mind I have over 6000 records, and over 20 columns.
It seems like it should be simple, but I can't really figure a way to do it.
Any help would be massively appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

It would be easier to simply delete the hacked rows and insert the repaired rows. If the ID column is important for identification of the row, you can use IDENTITY INSERT to temporarily allow the inserts to the ID column if it's and IDENTITY column.

--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)

Jeffrey Williams SSC Guru Points: 90258 More actions · Answer 2

You can use either of the following:

-- ANSI standard

UPDATE test

SET col1 = (SELECT col1

FROM test_2 t

WHERE t.pk = test.pk)

,col2 = (SELECT col2

FROM test_2 t

WHERE t.pk = test.pk)

,... repeat for all columns to be updated

WHERE test.pk IN (SELECT pk FROM test2);

-- SQL Server FROM

UPDATE t1

SET t1.col1 = t2.col1

,t1.col2 = t2.col2

,... repeat for all columns

FROM test t1

JOIN test_2 t2 ON t2.pk = t1.pk;

Both examples are not tested, so test thoroughly before running against your live table.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

keith-872606 Grasshopper Points: 24 More actions · Answer 3

Thanks, I'll give that a go and let you know how it turns out