Is there a way to incorporate an "UPDATE" statement inside a "SELECT" statement-CodePudding

I've added a new column to the ls_customer table that goes by response_category. I then set an update code to it.

ADD statement:

ALTER TABLE ls_customer
ADD response_category VARCHAR(255)

UPDATE Statement:

UPDATE ls_customer SET response_category = 'Hot Lead' WHERE flag = 3

UPDATE ls_customer SET response_category = 'Warm Lead' WHERE flag = 2

How do I set the above "UPDATE" statement to auto update whenever the flags change in our internal CRM tool?

I then use a SELECT statement to present this data. The problem is this "SELECT" statement is linked to my Tableau analytics. Everytime I refresh Tableau, if there's any changes in the flag, the response category doesn't get updated in the final SELECT statement untill I manually open MySQL and run the above "UPDATE" query again.

The SELECT statement is as follows:

SELECT
cell_phone,full_name,company_name,DATE(date_join),
response_type,response_category,ls_customer.tags AS lead_type,ls_lead_stats_raw_data.last_update,date_join,source 
FROM ls_lead_stats_raw_data
INNER JOIN ls_customer
ON ls_lead_stats_raw_data.client_id = ls_customer.customer_id
INNER JOIN ls_company
ON ls_company.company_id = ls_customer.company_id

How do I automate the UPDATE statement to set response_category to 'Hot Lead', 'Warm Lead' everytime I change the flags in our CRM?

CodePudding user response：

despite mysql version number, there is many different ways you can solve this issue, such as

creating a before update trigger on your table updating response_category column based on flag column content. But, maybe your CRM already use triggers and will not allow you to add any code to them
creating an database event executed every minute and updating the response_category column. this solution is not the most effective as it will execute the event, even nothing has changed in the flag column.
you can create a reference table with the flag and the response_category columns. It's content would be :

flag	response_category
2	warm lead
3	hot lead

and you add this new table with a join on ref_table.flag = ls_customer.flag to your select (replace response_category by ref_table.response_category). But this solution is so obvious, that I think there is a good reason you didn't use it.

So finally I would recommend to use a generated column instead of a regular column for response_category :

ALTER TABLE ls_customer

ADD COLUMN response_category VARCHAR(255) GENERATED ALWAYS

AS (CASE flag WHEN 2 THEN 'Hot lead' WHEN 3 THEN 'Warm lead' END)

STORED ;

using STORED option will allow you to add an index on this column in case you need to improve your SELECT performance.

Hope this will help