Solved

Trouble writing query

Posted on 2013-11-07
11
213 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
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 50 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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:hrolsons
ID: 39632439
Would it be :

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

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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 400 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 48

Accepted Solution

by:
PortletPaul earned 400 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

26 Experts available now in Live!

Get 1:1 Help Now