Cursor code example in MySQL to update from multiple rows in source

Example script to update multiple rows from source, where each row has the changed primary key.

How the keys look like? The highlighted columns form a composite primary key.
For
Update_Code = 1, New PK value updates Record_Key
Update_Code = 2, New PK value updates Date of Birth
Update_Code = 3, New PK value updates First Name

Cursor Update in MySQL dataset

Example script to solve using cursor:

drop procedure if exists segment_update_mbr;
DELIMITER $$
CREATE PROCEDURE segment_update_mbr(v_file_id bigint)
proc_label: begin

#-----------------------------------------------------------#
# Usage: Update segment_data using each row     #
#-----------------------------------------------------------#

declare done int default 0;
declare a, b int default 0;
declare v_mbr_id, v_mbr_rec_no, v_mbr_id_old varchar(500);
declare v_error, v_file_type varchar(500);
declare v_error_count,v_threshold , v_threshold_breach, v_staging_count, v_ref_id, v_update_count bigint;
DECLARE cur1 CURSOR FOR SELECT reference_id, member_id, member_record_number FROM temp_multiple_mc order by cast(member_record_number as unsigned integer);

# Ensure to cast your order by column to integer, else varchar columns will give incorrect order of rows

declare continue handler for not found set done=1;

set v_error = 'start';
set v_threshold = 0;
SET v_threshold_breach = 0;
SET v_staging_count=0;
SET v_error_count=0;
set v_ref_id = 0;
set v_update_count= 0;

SELECT segment_file_type_description
INTO v_file_type
FROM segment_file_type
WHERE segment_file_type_id = (SELECT segment_file_type_id FROM segment_file WHERE file_id = v_file_id);

    select account_id into @v_account
    from segment_file
    where file_id=v_file_id;


if lower(v_file_type) = 'complete' then

set sql_safe_updates = 0;

open cur1;
strt1 : loop
fetch cur1 into v_ref_id, v_mbr_id, v_mbr_rec_no;
#set v_error = 'line15';
if done = 1 then leave strt1;
end if;
select count(1) into a from segment_data where member_id = v_mbr_id;
select fn_check_key_present(v_file_id, v_ref_id) into @exist_chk;
select fn_check_duplicate_primary_mbr_present(v_file_id, v_ref_id) into @primary_chk;
select v_mbr_id, v_mbr_rec_no,v_ref_id, @exist_chk, @primary_chk, a;
 
if a > 0  and @exist_chk = 0  and @primary_chk = 0 then
set sql_safe_updates = 0;
set foreign_key_checks = 0;

# Update the segment_data table using the 1 row present in the cursor

update segment_data ed

inner join
segment_file_staging_complete as efsc
on ed.member_id = efsc.member_id
and ed.member_first_name = efsc.member_first_name
and cast(date_format(ed.date_of_birth, "%m%d%Y") as char(10))= cast(efsc.date_of_birth as char(10))
and ed.group_id = efsc.group_id
and ed.client_id = efsc.client_id
and ed.account_id = @v_account
inner join
temp_multiple_mc as tmm
on cast(tmm.reference_id as unsigned integer) = cast(efsc.segment_file_staging_complete_id as unsigned integer)
set
ed.member_id = case when efsc.maintenance_code = '01' and efsc.transition_id is not null and efsc.transition_id <> '' then efsc.transition_id else efsc.member_id end,
ed.date_of_birth = case when efsc.maintenance_code = '02' and efsc.transition_id is not null and efsc.transition_id <> '' then str_to_date(efsc.transition_id, "%m%d%Y") else str_to_date(efsc.date_of_birth, "%m%d%Y") end,
ed.member_first_name = case when efsc.maintenance_code = '03' and efsc.transition_id is not null and efsc.transition_id <> '' then efsc.transition_id else efsc.member_first_name end,
ed.modified_by = efsc.modified_by,
ed.modified_date = efsc.modified_date,
ed.file_id = efsc.file_id,
#ed.segment_file_type_id = efsc.segment_file_type_id,
ed.client_id = efsc.client_id,
#ed.account_id = efsc.account_id,
ed.initial_member_id = efsc.initial_member_id,
ed.member_middle_initial = efsc.member_middle_initial,
ed.member_last_name = efsc.member_last_name,
ed.group_id = efsc.group_id,
ed.segment_effective_date = str_to_date(efsc.segment_effective_date, "%m%d%Y"),
ed.segment_end_date = CASE WHEN efsc.segment_end_date = '' THEN '2099-01-01'  WHEN efsc.segment_end_date IS NULL THEN '2099-01-01'  ELSE str_to_date(efsc.segment_end_date, "%m%d%Y") END,
ed.gender_desc = (select lookup_value from lookup where lookup_group='GENDER' and lookup_key=efsc.gender)
where tmm.reference_id = v_ref_id
and efsc.is_invalid_row = 0
and efsc.is_duplicate_row = 0
;
set v_update_count = v_update_count + row_count();

set sql_safe_updates = 1;


# Mark records that on update will create duplicate primary key
elseif @exist_chk = 129 then
set sql_safe_updates = 0;
update temp_multiple_mc
set is_invalid_row = 1
where reference_id = v_ref_id;

set sql_safe_updates = 1;

elseif @primary_chk = 165 then
set sql_safe_updates = 0;
update temp_multiple_mc
set is_duplicate_row = 1
where reference_id = v_ref_id;

set sql_safe_updates = 1;
end loop;
end if;

end if;

# Updated rows count is inserted into segment_file_status table
set sql_safe_updates = 0;

select max(segment_file_status_id) into @status_id from segment_file_status where file_id = v_file_id;

update segment_file_status
set
records_updated = v_update_count
,modified_by = current_user()
where file_id = v_file_id
and segment_file_status_id>0
and segment_file_status_id = @status_id
;
SET SQL_SAFE_UPDATES=1;
end$$
DELIMITER ;

Like us on Google+ or Facebook and share your valuable feedback.