I'll walk through this solution bit by bit ...
Quote:
Query should be using "LIKE" operator where Column A1 like ('%B1%') OR Column A1 like ('%B2%')
.
Could be solved with
SELECT * FROM A INNER JOIN B ON A1 LIKE '%' + B2 + '%' OR A1 LIKE '%' + B1 + '%'
Quote:
If column A1 matched from B1 OR B2 then should skip looking for further matches
Use something like
SELECT TOP 1 * FROM A ...
which will stop once it has found a single matching row. Note that you can use this in conjunction with
ORDER BY ...
to refine it's behaviour.
Quote:
Also i want to mark that the column matched with B1 or B2
.I'm going to identify which column we used (B1 or B2) using a
CASE
statement like this
CASE WHEN CHARINDEX(B2, A1) > 0 THEN 'B2' ELSE 'B1' END AS WHICH
To pull of this together we're first going to identify the single matching row on table A with a sub-query and I'm going to call the results of that sub-query
TEMPRES
, then we're going to join A to TEMPRES to update the relevent row.
In the full example below I've assumed that you have some way of uniquely identifying each row in table A (this is usually a very good idea - have a look at the IDENTITY(s,i) property for a column). In my example I've called this
ID
UPDATE A
SET A1 = TEMPRES.WHICH
FROM A
INNER JOIN
(SELECT TOP 1 A.ID, CASE WHEN CHARINDEX(B2, A1) > 0 THEN 'B2' ELSE 'B1' END AS WHICH
FROM A INNER JOIN B ON A1 LIKE '%' + B2 + '%' OR A1 LIKE '%' + B1 + '%' ) TEMPRES ON A.ID=TEMPRES.ID
Finally, you might find these references helpful
http://www.akadia.com/services/sqlsrv_subqueries.html[
^]
http://msdn.microsoft.com/en-us/library/ms186775.aspx[
^]
[Edit - for some reason I started using column A2 instead of A1 ... corrected the sql]