Update Problem

Question

Update Problem

cmitchell-868382

SSC Journeyman

Points: 89
More actions
October 12, 2008 at 10:48 pm

#96553

I'm trying to do an up date but I can't get my head around it...
Update [myTable]
Set RevID = (Select otherTable.RevID from otherTable where Doc=otherTable.Doc and Rev=otherTable.Rev)
Where Doc is not Null
I need it to look at the 2 rows as if they were one though...
Select otherTable.RevID from [otherTable] where [Doc+Rev] = [otherTable.Doc + otherTable.Rev]
There are multiples of doc and rev but no multiples of the 2 combined...
Is this possible?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

The AND operator does what you want. By doing doc = doc AND rev = rev you are saying give me only records where BOTH doc and rev match. I typically write these types of statement like this:

Update MyTable

Set RevID = O.RevID

From

OtherTable O

Where

O.doc = MyTable.Doc And

O.rev = MyTable.Rev

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

cmitchell-868382 SSC Journeyman Points: 89 More actions · Answer 2

Thanks that worked a treat I was missing the "from other table" in my query...