Simple update query....

[robmason10]

Sorry in advance - have gone blank on what should be quite simple...

Basically i ave one table...

TypeID
Name
ReverseID

I want to update the ReverseID value with the TypeID value of another row - the criteria being the Name as I cannot use the TypeID due to it being a new insert on a live system..

i.e.

TypeID name ReverseID
1 Test 1 NULL
2 Test 2 NULL

So I am looking to update ReverseID for line 2 with the type ID from 1 and visa versa using the criteria WHERE name = Test 1

Required result:
TypeID name ReverseID
1 Test 1 2
2 Test 2 1

Something like
UPDATE tblRelationship SET ReverseID = [TypeID from line 1 based on = 'Test 1'] WHERE Name = Test 2

So how do I get [TypeID from line 1 based on = 'Test 1']??

 

[PHV]

Something like this ?
UPDATE tblRelationship
SET ReverseID = (SELECT TypeID FROM tblRelationship WHERE [name] = 'Test 1')
WHERE [name] = 'Test 2';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[robmason10]

Sweet - ta