-1

Table 1:

product_name brands Supplier
xyz Adidas
abc Nike

Table 2:

brands keys Suppliers
X x, y,z Puma,Adidas
A a New Balance

What I want to do is check if t1.product_name contains any t2.keys, if it does add t2.brands to t1.brands.

Code in python:

for index, key in enumerate(keys):
    for sub_index, sub_key in enumerate(key):

       for i, name in enumerate(product_name):

          if sub_key in name:
               Table1["brands"][i] =  Table1["brands"][index]

My questions:

  1. Is this possible in SQL?
  2. Is there a way I can use this script in SQL instead?
Roshan
  • 58
  • 8
  • 1
    Well, probably. To find out for sure, create a https://dbfiddle.uk/ with some sample data and update your post with the url and expected result. Never mind that the DBMS is not there, from the sound of it it should be possible with standard sql that will work in all DBMS – Lennart - Slava Ukraini Aug 25 '22 at 06:21
  • 2
    You can write loops, the question is do you want this? Consider a set based approach, this requires a different way of thinking about your problem but it will beat cursors. https://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursors – Preben Huybrechts Aug 25 '22 at 06:31
  • 3
    Snowflake is not SQL Server - please tag your RDBMS correctly. And as already said while can you loop (and there are plenty of tutorials and documentation out there), its very rare that you need to. Set based is what you should be aiming for. – Dale K Aug 25 '22 at 06:32
  • 1
    You can do this in a single statement, but unclear which platform you are using: Snowflake or SQL Server? – Charlieface Aug 25 '22 at 07:51
  • I am using Snowflake @Charlieface. – Roshan Aug 25 '22 at 07:57

1 Answers1

1

You can do this in a single statement. In Snowflake you can do it like this:

UPDATE Table1
SET brands = (
    SELECT LISTAGG(t2.brands, ', ')
    FROM Table2 AS t2
    WHERE EXISTS (SELECT 1
        FROM SPLIT_TO_TABLE(t2.keys, ',') AS s
        WHERE Table1.product_name LIKE '%' || TRIM(s.value) || '%'
    )
);

In SQL Server you would do it like this:

UPDATE t1
SET brands = (
    SELECT STRING_AGG(t2.brands, ', ')
    FROM Table2 t2
    WHERE EXISTS (SELECT 1
        FROM STRING_SPLIT(t2.keys, ',') s
        WHERE t1.product_name LIKE '%' + TRIM(s.value) + '%'
    )
)
FROM Table1 t1;
Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • How would you work on this problem now @Charlieface? I have added one extra dimension. If supplier from t2.suppliers match t1.supplier, then set t1. brands = t2.brands – Roshan Aug 25 '22 at 13:13
  • I suggest you post that as a new question. But you probably shouldn't be storing multiple pieces of information in a single column anyway – Charlieface Aug 25 '22 at 17:15
  • Thank you for your suggestion. Table 2 which has multiple values is a table that I want to use only to map Brand name for product name in table1. Other than that it doesnot has any use. – Roshan Aug 25 '22 at 17:26