0

I have a table with 7500+ records and every day is inserted about 300 new ones. Each new record can belong to a particular partner where each partner has its own idpartner.

Example of records: (partner A -> idpartner: 1 / partner B -> idpartner: 2 / partnerC -> idpartner: 3)

PartnerA-123

PartnerA-567

PartnerB-999

PartnerB-123

PartnerC-123

What I can not do is an event that runs every 10 minutes which will update the idpartner based on what comes before "-". If it's partnerA-, then idpartner = 1 and so on ...

I'm having to run an update every 10 minutes to keep this updated ... What I do:

update table set idpartner = 1 where name regexp 'partnerA-';

update table set idpartner = 2 where name regexp 'partnerB-';

update table set idpartner = 1 where name regexp 'partnerC-';

How to make an event that update the table every 10 minutes based on names with regexp?

Cesar Augusto
  • 169
  • 9
  • Just for making this more efficient , use `CASE EXPRESSION` : `UPDATE table t SET t.idpartner = CASE WHEN t.name regexp 'partnerA-' OR t..name regexp 'partnerC-' THEN 1 WHEN t.name regexp 'partnerC- THEN 2 ELSE t.idpartner END` – sagi Dec 06 '16 at 11:41
  • @sagi How could I put this inside an event that runs every 10 minutes? For me every time is giving delimiter error ... – Cesar Augusto Dec 06 '16 at 11:47
  • You could alternatively use a Trigger for every time a record is inserted , so google `MySQL after update Trigger` – sagi Dec 06 '16 at 12:04
  • Okay. And how would I do to make this trigger work only if the idpartner is -1? That is, `UPDATE table t SET t.idpartner = CASE WHEN t.name regexp 'partnerA-' OR t..name regexp 'partnerC-' THEN 1 WHEN t.name regexp 'partnerC- THEN 2 ELSE t.idpartner END` only if idpartner = -1? – Cesar Augusto Dec 06 '16 at 12:09
  • `UPDATE table t SET t.idpartner = CASE WHEN t.name regexp 'partnerA-' OR t.name regexp 'partnerC-' THEN 1 WHEN t.name regexp 'partnerC- THEN 2 ELSE t.idpartner END WHERE t.idpartner = -1` – sagi Dec 06 '16 at 12:31

0 Answers0