?
Solved

Trouble writing query

Posted on 2013-11-07
11
Medium Priority
?
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 69

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

777 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