How to cycle trough result set in MySQL 8 while updating the table?

33
September 14, 2021, at 01:10 AM

I have

  • Table A: Known products with product_id and title
  • Table B: Scraped product infos with title and URL

My goal now is to find product_id belonging to found products from Table B and save this prouduct_id to Table B.

Finding a product is easy via full text search:

SELECT
    mp.SKU
FROM
    my_products mp
WHERE
    MATCH (mp.NAME, mp.QUANTITY) AGAINST ('Some title I search for')
LIMIT 1

Now trying to go through all found products:

SELECT TITLE, URL,(
                    SELECT
                        mp.SKU
                    FROM
                        my_products mp
                    WHERE
                        MATCH (mp.NAME, mp.QUANTITY) AGAINST (a.TITLE)
                    LIMIT 1
           ) SKU
from amazon_products a
where SKU is null

This obviously does not work as I can not reference the outer table. Not mentioning to updating the table and adding the product_id to Table B. How can this be done?

Answer 1

As you can see in the documentation

The search string must be a string value that is constant during query evaluation.

this why you query will not work and has nothing to do with subquery..

Only a LIKE or REGEXP expression could handle this.

A stored procedure with dynamic sql could handle this also. but would take a hell of a time to loop thrugh all posibilities

Answer 2

You may try the following update statement.

Recommended

The MATCH-AGINST requires a literal value in the against and so a like function may be more ideal here. It may expensive to loop through your resultset and each time querying another table. Below I've used a LIKE with wildcard match to achieve this in the update function.

Schema (MySQL v8.0)

create table amazon_products (
    product_id int,
    title varchar(20),
    FULLTEXT KEY (title)
);
create table my_products (
    product_id int,
    NAME text,
    QUANTITY varchar(20) default 'some',
    FULLTEXT(NAME, QUANTITY)
);
insert into my_products (product_id,name) values (1,'cardboard'),(2,'tes');
insert into amazon_products values (1,'cars'),(NULL,'test');

Query #1

select * from amazon_products;
product_id title
1 cars
test

Query #2

UPDATE amazon_products a,
       my_products mp
SET a.product_id = mp.product_id
WHERE
    a.title LIKE CONCAT('%',mp.NAME,'%')
    AND
    a.product_id IS NULL;

There are no results to be displayed.

Query #3

select * from amazon_products;
product_id title
1 cars
2 test

View on DB Fiddle

Out of Curiosity, Not recommended

Out of curiosity, I've created a function to achieve this but this may be more expensive.

CREATE FUNCTION fn_get_matching_id(
    TITLE TEXT
)
RETURNS INT NOT DETERMINISTIC 
BEGIN
    DECLARE matching_id INT;
    SELECT mp.product_id 
    INTO matching_id
    FROM my_products mp
    WHERE MATCH(mp.NAME,mp.QUANTITY) AGAINST (TITLE IN NATURAL LANGUAGE MODE)
    ORDER BY MATCH(mp.NAME,mp.QUANTITY) AGAINST (TITLE IN NATURAL LANGUAGE MODE) DESC
    LIMIT 1;
    RETURN (matching_id);
END

Testing

select * from amazon_products;
product_id | title
---------: | :----
         1 | cars 
      null | test 
select a.*, fn_get_matching_id(a.title)  as suggested_id from amazon_products a;
product_id | title | suggested_id
---------: | :---- | -----------:
         1 | cars  |         null
      null | test  |            2
explain select a.*, fn_get_matching_id(a.title)  as suggested_id from amazon_products a;
id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra
-: | :---------- | :---- | :--------- | :--- | :------------ | :--- | :------ | :--- | ---: | -------: | :----
 1 | SIMPLE      | a     | null       | ALL  | null          | null | null    | null |    2 |   100.00 | null 
explain analyze select a.*, fn_get_matching_id(a.title)  as suggested_id from amazon_products a;
| EXPLAIN                                                                              |
| :----------------------------------------------------------------------------------- |
| -> Table scan on a  (cost=0.45 rows=2) (actual time=0.009..0.017 rows=2 loops=1)<br> |
UPDATE amazon_products a
SET a.product_id = fn_get_matching_id(a.title)
WHERE
    a.product_id IS NULL;
    
select * from amazon_products;
product_id | title
---------: | :----
         1 | cars 
         2 | test 

db<>fiddle here

Let me know if this works for you.

READ ALSO
How to add a folder to gallery in React Native

How to add a folder to gallery in React Native

I'd like to add a folder to the gallery in React Native, like Instagram or WhatsApp have oneCould you tell me how to do that in React Native for Android and iOS ?

33
Prevent Hidden Bootstrap 4 Modals to react events

Prevent Hidden Bootstrap 4 Modals to react events

I am trying to create a "Confirmation Modal" on deleting a row in a tableWhen the user wants to delete a row a Modal is shown (on click of a button) in which the user must type the username as a confirmation to delete it and click the delete...

59
How to display an image using RowGroup?

How to display an image using RowGroup?

I'm using the RowGrouping add-on, all the rows are correctly grouped as shown below:

40