2

I want to update column price in table but I can't.

if C1 like N then update p1 with p2

a small example,but real .

190000 rows

TBL_1 :

    |     id     |Name        |                   C1                                  | P1       |
-----------------------------------------------------------------------------------------------------------
    |      29214 | g6 1325sr  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null    |
    |      29215 | g6 1341se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null    |
    |      29226 | g6 1342se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null    |
    |      29237 | g6 1346se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null    |
    |      29248 | g6 1321sr  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null    |
    |      29259 | g6 1345se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null    |
    |      29269 | N56 1341se |         N56-11, N56-21, N56-1100, N56-2980, N56-2230, | Null    |
    |      29270 | N56 1348se |         N56-11, N56-21, N56-1100, N56-2980, N56-2230, | Null    |
    |      29271 | F566 1341se|         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null    |

TBL_2 :

    |     id     |            N              | EditName    |      P2    | 
--------------------------------------------------------------------------
    |      1     | A N56                     | N56         |     32000 |
    |      2     | H G6-2000                 | G6          |     28000 |
    |      3     | H DV3-4000                | DV3         |     42000 |
    |      4     | Z510                      | Z510        |     52000 |
    |      5     | SA                        |  SA         |     38000 |
    |      6     | A 1278-1369-1370-1465-1466| 1278        |     63000 |
    |      7     | A w7                      |  w7         |     52000 |
    |      8     | H  4540                   |   4540      |     15000 |
    |      9     | A 1400                    |    1400     |     12000 |

The result is:

    |     id     |Name        |                   C1                                  | P1       |
-----------------------------------------------------------------------------------------------------------
    |      29214 | g6 1325sr  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | 28000    |
    |      29215 | g6 1341se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | 28000    |
    |      29226 | g6 1342se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | 28000    |
    |      29237 | g6 1346se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | 28000    |
    |      29248 | g6 1321sr  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | 28000    |
    |      29259 | g6 1345se  |         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | 28000    |
    |      29269 | N56 1341se |         N56-11, N56-21, N56-1100, N56-2980, N56-2230, | 32000    |
    |      29270 | N56 1348se |         N56-11, N56-21, N56-1100, N56-2980, N56-2230, | 32000    |
    |      29271 | F566 1341se|         g6-1, g6-2, g6-1000, g6-1980, g6-1230,        | Null     |

try:

select [A].id , [A].C1, [A].P1, [B].P2 
from [dbo].[TBL_1] [A]
inner join [dbo].[TBL_2] [B]
on [A].C1  LIKE CONCAT('%', [B].N, ',%')

-- on [A].com  LIKE '%N56,%'

in this try ,for each rows join all rows in TBL_2

2
  • 1
    Based on the example that you provided, the join is going to be "fuzzy logic" and imprecise. How accurate do you need the join to be? It looks like you might have an easier time matching on the "Edit Name" field rather than the "N" field as well. Feb 26, 2017 at 16:24
  • thanks for tips but I don't know how to join this example
    – RedArmy
    Feb 26, 2017 at 18:19

1 Answer 1

3

I've set up a sample here.

Having a look at your data, if you use N:

SELECT     t1.id, t1.Name, t1.C1, t2.N, t2.EditName
FROM       #tbl1 t1
INNER JOIN #tbl2 t2
ON         t1.C1 LIKE '%' + t2.N + '%';

It doesn't return any row. However, if you use EditName:

SELECT     t1.id, t1.Name, t1.C1, t2.N, t2.EditName
FROM       #tbl1 t1
INNER JOIN #tbl2 t2
ON         t1.C1 LIKE '%' + t2.EditName + '%';

You get:

+-------+-------------+-----------------------------------------------+-----------+----------+
|   id  | Name        | C1                                            | N         | EditName |
+-------+-------------+-----------------------------------------------+-----------+----------+
| 29269 | N56 1341se  | N56-11, N56-21, N56-1100, N56-2980, N56-2230, | A N56     | N56      |
| 29270 | N56 1348se  | N56-11, N56-21, N56-1100, N56-2980, N56-2230, | A N56     | N56      |
| 29214 | g6 1325sr   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | H G6-2000 | G6       |
| 29215 | g6 1341se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | H G6-2000 | G6       |
| 29226 | g6 1342se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | H G6-2000 | G6       |
| 29237 | g6 1346se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | H G6-2000 | G6       |
| 29248 | g6 1321sr   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | H G6-2000 | G6       |
| 29259 | g6 1345se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | H G6-2000 | G6       |
| 29271 | F566 1341se | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | H G6-2000 | G6       |
+-------+-------------+-----------------------------------------------+-----------+----------+

And after update it using:

UPDATE     #tbl1
SET        P1 = t2.P2
FROM       #tbl1 t1
INNER JOIN #tbl2 t2
ON         t1.C1 LIKE '%' + t2.EditName + '%';

The final result is:

+-------+-------------+-----------------------------------------------+-------+
| id    | Name        | C1                                            | P1    |
+-------+-------------+-----------------------------------------------+-------+
| 29214 | g6 1325sr   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
| 29215 | g6 1341se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
| 29226 | g6 1342se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
| 29237 | g6 1346se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
| 29248 | g6 1321sr   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
| 29259 | g6 1345se   | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
| 29269 | N56 1341se  | N56-11, N56-21, N56-1100, N56-2980, N56-2230, | 32000 |
| 29270 | N56 1348se  | N56-11, N56-21, N56-1100, N56-2980, N56-2230, | 32000 |
| 29271 | F566 1341se | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
+-------+-------------+-----------------------------------------------+-------+
3
  • ok thats good @McNets , but in like very long for me beacuse For some fields require special Likes.
    – RedArmy
    Feb 27, 2017 at 8:25
  • @RedArmy maybe you should redefine the question or better add a new one.
    – McNets
    Feb 27, 2017 at 8:32
  • Count of rows data is 17000 and maybe not seen more data for question.
    – RedArmy
    Feb 27, 2017 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.