hrolsons
asked on
Trouble writing query
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?
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?
ASKER
I like your MERGE idea, but not sure how to write the query.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So in the real database I have this so far:
UPDATE photos_new set photos_new.pic=photos_new_ Backup_06_ 11_2012.pi c
where photos_new.bookmark=photos _new_Backu p_06_11_20 12.bookmar k
and not photos_new.pic=photos_new_ Backup_06_ 11_2012.pi c
and photos_new_Backup_06_11_20 12.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?
UPDATE photos_new set photos_new.pic=photos_new_
where photos_new.bookmark=photos
and not photos_new.pic=photos_new_
and photos_new_Backup_06_11_20
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?
ASKER
Would it be :
photos_new.pic NOT LIKE '%[^0-9]%'
photos_new.pic NOT LIKE '%[^0-9]%'
??photos_new.pic is an integer
what is the actual data type of photos_new.pic?
e.g.
varchar
decimal
what is the actual data type of photos_new.pic?
e.g.
varchar
decimal
ASKER
It is varchar. The records that I screwed up I wrote an integer to that field.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good suggestion with the Select. I tried:
And got the error:
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]%';
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.