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.
