How to apply an Updatable CTE that will Change Grouped Numbering and Apply it to two tables?-CodePudding

I have two tables with multiple rows:

Table A
(TYPE - Num)
LA - 1
LA - 2
LA - 3
LA - 4
LA - 5
MB - 1
MB - 2
.
.
.

Table B
(TYPE - Num - NumLine)
LA - 1 - 1
LA - 1 - 2
LA - 1 - 3
LA - 1 - 4
LA - 2 - 1
LA - 2 - 2
LA - 2 - 3
LA - 2 - 4
LA - 3 - 1
LA - 3 - 2
LA - 4 - 1
LA - 5 - 1
MB - 1 - 1
MB - 1 - 2
MB - 1 - 3
MB - 2 - 1
.
.
.

And I want the NUM to start from a specific number for LA and a different number for MB: Example: I want LA to start from 12 and MB to start from 20, and I want this change to apply to both tables, on the corresponding row.

Table A
(TYPE - Num)
LA - 12
LA - 13
LA - 14
LA - 15
LA - 16
MB - 20
MB - 21
.
.
.

Table B
(TYPE - Num - NumLine)
LA - 12 - 1
LA - 12 - 2
LA - 12 - 3
LA - 12 - 4
LA - 13 - 1
LA - 13 - 2
LA - 13 - 3
LA - 13 - 4
LA - 14 - 1
LA - 14 - 2
LA - 15 - 1
LA - 16 - 1
MB - 20 - 1
MB - 20 - 2
MB - 20 - 3
MB - 21 - 1
.
.
.

How can I do this? I want to apply this for a lot of rows.

CodePudding user response：

If It is for selection you can try something like below

Select Type, Num
From(
Select *,CASE WHEN TYPE = 'LA' and  Num >11 then 1 
              WHEN TYPE = 'MB' and  Num >19 then 1 
              ELSE 0 END AS flag
FROM TableA
) A
Where flag =1

CodePudding user response：

If you have a foreign key between the two tables, you should change it to ON UPDATE CASCADE.

ALTER TABLE TableB ADD CONSTRAINT fk_Type_Num
  FOREIGN KEY (Type, Num)
  REFERENCES TableA(Type, Num)
  ON UPDATE CASCADE;

Then you can simply do

UPDATE TableA
SET Num = Num   CASE WHEN Type = 'LA' THEN 11 ELSE 19 END
WHERE Type IN ('LA','MB');

This will automatically update TableB as well.