Posted by Roy Harvey on 12/27/06 23:33

Interesting. Maybe this will give you and angle to try.


UPDATE B
SET EmployeeLocationID =
(SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL

Roy Harvey
Beacon Falls, CT

On 27 Dec 2006 15:24:44 -0800, varkey.mathew@wipro.com wrote:

>Dear all,
>
>I need some help from all my Transact SQL Guru friends out there..
>
>Here is the scenario in its most simplified form.. ..
>
>I have two tables.. A(Lookup table) and B(Transaction Table)
>
>TableA Fields
> EmployeeLocationID
> EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>
>TableB Fields
> EmployeeID
> EmployeeName.......
> EmployeeLocationID (will have null initially when rows are populated
>first time)
> EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK".... etc)
>
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>
>Therefore, this is what I had wrote initially..
>
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match.... etc....
>
>Therefore this is the expected match for my examples based on
>requirement..
>
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>
>Looking forward to hearing from someone ASAP.. Please help..
>
>Best regards,
>
>VM...

[Back to original message]