I frequently use a lookup query as follows:
UPDATE
(SELECT [D].[Column 1] AS Target, [LUT].[Column 1] As Source
FROM [D], [LUT]
WHERE [LUT].[Column 2] LIKE [D].[Column 2]
)
SET [Target] = [Source];
My problem is that [LUT].[Column 2] field often contains several tokens e.g. AAA; BBB; CCC; while [D].[Column 2] has a single token to match to e.g. BBB;
What would the syntax be to single out the matching token?
Thanks
Aussie Nature Shots
Can you put that another way Colin?
That query will only match and update rows where the [Column 2] values are aLIKE. (E.g. BBB*.)
What result do you need?
Tim, I need the query to 'see' BBB; in say AAA; BBB; CCC; in [LUT].[Column 2] as the match for BBB; in [D].[Column 2] so it then writes the associated value of [LUT].[Column 1] into [D].[Column 1]
The structure of the tokens is as written including the semicolon. There are anywhere from 1 - 6 tokens in the LUT and the match can be in any position from first through to last.
Ah!
Colin,
something like this?
WHERE POSITION([D].[Column 2] IN [LUT].[Column 2])>0
Excellent Walter, thanks!!