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
Solved

Trouble writing query

Posted on 2013-11-07
11
216 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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
 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when creating an UPDATE Trigger in SQL 6 21
Database Integrity 1 50
TSQL XML Namespaces 7 24
Reformat SQL - so SSRS can read the columns 25 12
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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