Subscribe to this thread
Home - General / All posts - Lookup query
ColinD

2,081 post(s)
#18-Aug-15 08:25

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

tjhb
10,094 post(s)
#18-Aug-15 12:00

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?

ColinD

2,081 post(s)
#18-Aug-15 12:43

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.


Aussie Nature Shots

tjhb
10,094 post(s)
#18-Aug-15 12:48

Ah!

walter
20 post(s)
#18-Aug-15 16:21

Colin,

something like this?

UPDATE

    (SELECT [D].[Column 1] AS Target, [LUT].[Column 1] As Source

    FROM [D][LUT]

    WHERE POSITION([D].[Column 2] IN [LUT].[Column 2])>0 

    )

SET [Target] = [Source];

ColinD

2,081 post(s)
#18-Aug-15 23:07

Excellent Walter, thanks!!


Aussie Nature Shots

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.