1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
rain_
  • 734
  • 1
  • 13
  • 24

2 Answers2

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for your answer. It is an honour get answers from such an experienced engineer. I followed your advise and managed to find a way to do the task avoiding that comma separated field. I accept this answer for encourage me to do so. – rain_ Oct 24 '17 at 07:17
0

Could you do:

UPDATE first_table ft
INNER JOIN second_table st
ON ft.code=st.code
AND st.store LIKE CONCAT('%', ft.store ,'%')
SET ft.sent = 1
Spencer Bard
  • 1,015
  • 6
  • 10