Solved

Trouble writing query

Posted on 2013-11-07
11
209 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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
I like your MERGE idea, but not sure how to write the query.
0
 
LVL 9

Assisted Solution

by:guswebb
guswebb earned 50 total points
Comment Utility
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:hrolsons
Comment Utility
Would it be :

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

Expert Comment

by:PortletPaul
Comment Utility
??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
Comment Utility
It is varchar.  The records that I screwed up I wrote an integer to that field.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
Comment Utility
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
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 400 total points
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now