Hey folks
I am having a problem getting my head around an embedded query, any suggestions on how to achieve the desired results.
I have a temp table in a stored procedure that is populated from another database table. However there is info missing and the temp table is left with a string 'TBC' in one of the fields (GT). So I am trying to write a query that can lookup the correct GT Code using a field in the temp table.
There is a lookup table, that has postcode as a PK and the needed code as a column. SO I tried looking up based on the postcode. The problem is my embedded select brings back all fields where posts codes match. Is there any way to make it match just the 1 row at a time without a loop? Currently I get an error due to too many results returned from the subquery. each row will have a postcode to match and a GT code to find.
Hope I explained that alrigtht
[code]
UPDATE #tmptable SET GT=(SELECT l.gtCode from LookupTable l JOIN #tmptable t ON t.postcode = l.postcode WHERE Postcode=PostCode
[/code]
Good to see you got back in 
FYI, we're using markdown instead of BBCode these days. Also, if you want to get fancy with the code, you can embed gists in your posts like so:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have changed the postcode column on the lookup table to pcode to try and avoid confusion. removing the WHERE clause in the embedded select didnt work, it still matches too many results.
The lookup table postcode are the first 4 characters, for example EH10, EH11, GL10 etc and I am matching them to LEFT(Postcode, 4) which should return the same, problem is it will match ever row in the table in the embedded select like that. I am thinking it might be easier to do in a loop with VB rather than have the SQL do the work...