Copying data in two tables with foreign keys-CodePudding

I have two tables, reviews and grade.

Reviews table has

id_review (primary key), id_lang, email, text etc.

Example

1, 2, email@email.com, test text
2, 2, email@email.com, test text
4, 2, email@email.com, test text

Grade table has

id_review (primary/foreign key), id_criterion, grade

1, 3, 5.00
1, 1, 4.00
2, 3, 3.00
2, 1, 5.00
4, 2, 3.00

I need to copy all the reviews with lang id 2, change the text and the lang id to 1 (this I can do manually). But as the id_review changes with the copied reviews, I need to create new rows on the grade table, too. Is there a way to make sure that the foreign keys are matched with the copied reviews, too?

I tried to do it the old fashioned way with copy/paste on csv but as some reviews are removed from the reviews table and some reviews have differences in id_criterion count, it's very hard to do for a large table.

Or should I try to edit the table to allow the reviews table to have distinct values for id_lang with the same id_review?

CodePudding user response：

You can create temporary tables (no foreign keys) out of the original ones, patch and validate the data until you satisfy and then insert back to the original tables.

I am not sure how you populate id_review, so I assume they are auto generated when you insert new rows.

create table reviews_temp_20211119 as
select r.id_review as old_id_review
, 0 as new_id_review
, row_number() over(order by r.id_review) as ref_patch_id
, r.id_lang
, r.email
, r.text
from reviews r
where id_lang = 2;

create table grades_temp_20211119 as
select g.id_review
, g.id_criterion
, g.gradate
, 0 as new_id_review
from grades g
where g.id_review in (select t.old_id_review from reviews_temp_20211119 t);

update reviews_temp_20211119
set id_lang = 1;

alter table reviews add column ref_patch_id bigint null;

-- insert back to original to get the auto generated id_review
-- if you use other strategies to populate the id_review, you can do update it directly to the temp table and review if all the data are correct before insert back to the original table
insert into reviews (id_lang, email, text, ref_patch_id)
select id_lang, email, text, ref_patch_id
from reviews_temp_20211119;

update reviews_temp_20211119 t
join reviews r on (r.ref_patch_id = t.ref_patch_id)
set t.new_id_review = r.id_review;

update grades_temp_20211119 g
join reviews_temp_20211119 t on (g.id_review = t.old_id_review)
set g.new_id_review = t.new_id_review);

insert into grades (id_review, id_criterion, grade)
select t.new_id_review
, t.id_criterion
, t.grade
from grades_temp_20211119 t;

By keeping the temporary tables, you have opportunity to review or rollback the change if something went wrong by looking back at the temporary tables.

CodePudding user response：

For a repeatable process, I think a stored procedure with cursors is the way. Here's my version, it accept two parameters, the old idLang you wish to copy, and the new idLang:

CREATE PROCEDURE copyReviewWithNewLang(IN oldidLang INT, IN newidLang INT)

BEGIN
DECLARE c_idReview, c_maxIdReview INT;
DECLARE c_text, c_email VARCHAR;
DECLARE old_c_idreview INT DEFAULT 0;

--  first cursor gets all the review rows of the old language, ordered
DECLARE rev_cur CURSOR FOR SELECT idReview, email, text FROM reviews WHERE id_lang = oldidLang ORDER BY idReview ASC;
-- second cursor gets the highest idReview
DECLARE maxid_cur CURSOR FOR SELECT MAX(idReview) FROM  reviews;

-- needed for ending the loop on end of retrieved data
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN rev_cur;

retrieving : LOOP

    FETCH rev_cur INTO c_idReview, c_email, c_text;
    
    -- ending the loop
    IF done THEN
        LEAVE retrieving;
    END IF;
    
    IF (old_c_idreview = 0) OR (old_c_idreview != c_idReview) THEN
        OPEN maxid_cur;
        FETCH maxid_cur INTO c_maxIdReview;
        CLOSE maxid_cur;
        SET c_maxIdReview = c_maxIdReview   1
        
    END IF;
    
    -- copying the review row
    INSERT INTO reviews (id_review, id_lang, email, text)
    VALUES(c_maxIdReview, newidLang, c_email, c_text)
    
    -- copying the grade rows
    INSERT INTO grades (id_review, id_criterion, grade)
    SELECT c_maxIdReview, id_criterion, grade FROM grades
    WHERE id_review = c_idReview;
    
    -- needed for checking if id changed
    SET old_c_idreview = c_idReview;
    
END LOOP;

CLOSE rev_cur;

END;