[inetgnu]

Hello,

I have the following problem of getting the SQL statement of the following
task:

table 1: part_of_car
--------------------------------------------------
pid | pdesc | pcategory
--------------------------------------------------
100 | AR-R8-4D NSK BEARING |
200 | 110-702-3S WATER PUMP |
300 | 1764-FJ-3 DISC BRAKE PAD KIT |
400 | INSULATOR ENG KTB |
...


table2: category
------------------------------
cid | keyword | desciptiontext
------------------------------
1 | CYL | Cylinder
2 | BRK | Brake
3 | ENG | Engine
4 | BUM | Bumper
5 | BEA | Bearing
6 | PUM | Pump
...


The problem is to search the 'pdesc' column of 'part_of_car' table containing
the text of each of value of the 'descriptiontext' of the 'category' table and UPDATE 'part_of_car'.'pcategory' column with the corresponding 'keyword' column of the 'category' table.


The UPDATED 'part_of_car' Table is expected to be :


pid | pdesc | pcategory
-------------------------------------------------
100 | AR-R8-4D NSK BEARING | BEA
200 | 110-702-3S WATER PUMP | PUM
300 | 1764-FJ-3 DISC BRAKE PAD KIT | BRK
400 | A4-BT INSULATOR ENGINE | ENG

Thank you for your help in advance.

Regards

[Littlefoot]

What if part_of_car.pdesc contains two (or more) category.descriptiontexts? For example, "1764-FJ-3 ENGINE BRAKE PAD KIT" (never mind this example means nothing in real life). What would you take as a result for part_of_car.pcategory? ENG or BRK?

If those tables contain a few data, I guess result will be more accurate if you do it yourself (scroll through data and mark them).
But, if it is impossible, I'm afraid you might have a table full of garbage.

However ... I'd say you need two nested loops: first would read part_of_car table and extract, as substrings, words of pdesc column ("110-702-3S WATER PUMP " = "110-702-3S" + "WATER" + "PUMP") and then, in another loop, compare that substring with all the category.descriptiontext values. If they match, update part_of_car table with the category.keyword you've found.

Quite simple, actually ... If that's what you need, of course