Update records in one table for register present in another table, in a comma separated field

Question

I have two tables:

FirstTable:

code    store  sent
1000002 Store1 0 
1000002 Store2 0
1000002 Store3 0

SecondTable:

code    stores
1000002 Store1,Store3

What I want to do is to update FirstTable.sent for an specific code, only for the records present in SecondTable.stores

I have a procedure that takes a string and a separator and builds a resultset from the string. For example if I make the following call:

call sp_csv_as_resultset("A,B,C,D", ",");

I get the following output as a result set:

A
B
C
D

I made that procedure while working in the next update statement, which aims to achieve what I need:

update FirstTable ft
inner join et_sap_magento_store_ids_removals_IF st
on ft.code = st.code
set ft.sent = 1
and ft.sent = 0
and ft.store in (sp_csv_as_resultset(st.stores, ','));

But as you know, you cannot call a stored procedure within a select.

I think a function does not cover my needs either because it cannot return a table or a resultset. Is there a way to do what I need without a design change?

Answer 1

MySQL has a function called FIND_IN_SET() that can help.

UPDATE FirstTable AS f
JOIN SecondTable AS s
  ON f.code = s.code AND FIND_IN_SET(f.store, stores)
SET f.sent =1
WHERE f.sent = 0;

However, be aware that FIND_IN_SET() cannot use an index. In your case, at least the join can use an index on code but within those matches it must do a scan the hard way.

There are lots of questions on Stack Overflow about joining on comma-separated strings. I couldn't find one that mentioned an UPDATE query, or else I would have voted to close your question as a duplicate.

The better answer is to avoid storing comma-separated strings when you know you need to reference individual entries in that list in SQL conditions. See my answer to Is storing a delimited list in a database column really that bad?