?
Solved

Trouble writing query

Posted on 2013-11-07
11
Medium Priority
?
225 Views
Last Modified: 2013-11-08
So I screwed up and overwrote some data that I didn't want to.  Fortunately I have a backup, but I'm having trouble writing the query.

Let's say we have Table1 and Field1 is the one I messed up.  I only messed up about 500 records out of a million.  I only want to restore those 500 because the backup is a little dated.

And let's say the backup is Table1_backup with a Field1.

How would I go about writing this query?  Any ideas?
0
Comment
Question by:hrolsons
11 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39631215
1. You can truncate the Table1 and reload from Backup table.
2. You can write a MERGE script to join two tables on Primary key and update the records when Field1 from both the tables are not matching.
0
 

Author Comment

by:hrolsons
ID: 39631324
I like your MERGE idea, but not sure how to write the query.
0
 
LVL 9

Assisted Solution

by:guswebb
guswebb earned 200 total points
ID: 39632243
UPDATE Table1 set Table1.Field1=Table1_backup.Field1 where Table1.id = Table1_backup.id and not Table1.Field1=Table1_backup.Field1

This assumes that both Table1 and Table1_backup have a primary key called 'id' upon which you can bind the two together.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 39632365
Do you have some way in the row's data to identity the 500 rows that you want to change vs the rest of the ~1M rows you don't?  If you do, the UPDATE statement itself will be easy to code, given some type of unique column(s) to join the tables on.
0
 

Author Comment

by:hrolsons
ID: 39632424
So in the real database I have this so far:

UPDATE photos_new set photos_new.pic=photos_new_Backup_06_11_2012.pic  
where photos_new.bookmark=photos_new_Backup_06_11_2012.bookmark
and not photos_new.pic=photos_new_Backup_06_11_2012.pic
and photos_new_Backup_06_11_2012.pic is not null;

The last thing I need to throw in is that we can only change the value if:

photos_new.pic is an integer

Can you help me throw that in there?
0
 

Author Comment

by:hrolsons
ID: 39632439
Would it be :

photos_new.pic NOT LIKE '%[^0-9]%'
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39632444
??photos_new.pic is an integer

what is the actual data type of photos_new.pic?
e.g.
varchar
decimal
0
 

Author Comment

by:hrolsons
ID: 39632465
It is varchar.  The records that I screwed up I wrote an integer to that field.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1600 total points
ID: 39632480
hrolsons's answer should do it then

(ps: I had not seen that comment until after I added mine)

test by running as a select first :)
0
 

Author Comment

by:hrolsons
ID: 39632601
Good suggestion with the Select.  I tried:

select * from photos_new 
where photos_new.bookmark=photos_new_Backup_06_11_2012.bookmark 
and not photos_new.pic=photos_new_Backup_06_11_2012.pic 
and photos_new_Backup_06_11_2012.pic is not null and photos_new.pic NOT LIKE '%[^0-9]%';

Open in new window


And got the error:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "photos_new_Backup_06_11_2012.bookmark" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "photos_new_Backup_06_11_2012.pic" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "photos_new_Backup_06_11_2012.pic" could not be bound.

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1600 total points
ID: 39632629
try this, I think you forgot the from clause
/* try select first */
SELECT
        *
FROM photos_new_Backup_06_11_2012
   , photos_new
WHERE photos_new.bookmark = photos_new_Backup_06_11_2012.bookmark
        AND NOT photos_new.pic = photos_new_Backup_06_11_2012.pic
        AND photos_new_Backup_06_11_2012.pic IS NOT NULL
        AND photos_new.pic NOT LIKE '%[^0-9]%';

UPDATE
        photos_new
SET
        photos_new.pic = photos_new_Backup_06_11_2012.pic
FROM photos_new_Backup_06_11_2012
WHERE photos_new.bookmark = photos_new_Backup_06_11_2012.bookmark
        AND NOT photos_new.pic = photos_new_Backup_06_11_2012.pic
        AND photos_new_Backup_06_11_2012.pic IS NOT NULL
        AND photos_new.pic NOT LIKE '%[^0-9]%';

Open in new window

0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question