Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Smile Unanswered: Update table from an lookup table

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •