0

I want to apply these updates to multiple tables (ba, cd, cc, ca) but not sure how to do this. I tried:

drop procedure if exists updateCCCat;
delimiter //
create procedure updateCCCat(tbl varchar(20))
begin
    update tbl set cat='Alcohol',sub_cat='Liquor' where d like 'total wine%' or d like '%liquor%';
    update tbl set cat='Alcohol',sub_cat='Wine' where d like '%winery%';
    update tbl set cat='Automotive',sub_cat='Repairs' where d like '%nissan%' or d like '%firestone%';
    update tbl set cat='Automotive',sub_cat='Gas' where d like '%costco gas%' or d like '%oil%' or d like '% bp %';
end //
delimiter ;

but that didn't work.

I also tried in the above, using the variable @newtbl:

set @newtbl = tbl

But that also didn't work either

drop procedure if exists updateCCCat;
delimiter //
create procedure updateCCCat()
begin
    update ba set cat='Alcohol',sub_cat='Liquor' where d like 'total wine%' or d like '%liquor%';
    update ba set cat='Alcohol',sub_cat='Wine' where d like '%winery%';
    update ba set cat='Automotive',sub_cat='Repairs' where d like '%nissan%' or d like '%firestone%';
    update ba set cat='Automotive',sub_cat='Gas' where d like '%costco gas%' or d like '%oil%' or d like '% bp %';
end //
delimiter ;
Rob Campbell
  • 31
  • 6
  • I think you want the table names to be dynamic based on a parameter passed to the procedure - in which case you need to use dynamic sql - see https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html – P.Salmon Feb 22 '22 at 14:33
  • Do I need to have a set, prepare and execute line for every update that I'm trying to do? I have 68 updates to do for each table (possibly). I'm applying categories and sub-categories to each row for 6 tables. – Rob Campbell Feb 22 '22 at 19:52

0 Answers0