How to copy specific rows from the table-CodePudding

I have the following table

CREATE TABLE DMS
(
KTOV char(10),
NDM int,
KOL decimal(13,2) NOT NULL,
CENA decimal(13,2) NOT NULL,
CONSTRAINT FK_KTOV FOREIGN KEY(KTOV)
REFERENCES TOV(KTOV),
CONSTRAINT FK_NDM FOREIGN KEY(NDM)
REFERENCES DMZ(NDM)
);

I filled it with some handwritten data

INSERT INTO DMS VALUES('101', 1, 20, 10.00, 'Light');
INSERT INTO DMS VALUES('102', 1, 10, 8.00, 'Dark');
INSERT INTO DMS VALUES('103', 1, 30, 5.00, 'Paprika');
INSERT INTO DMS VALUES('101', 2, 10, 6.00, 'Light');
INSERT INTO DMS VALUES('102', 3, 20, 8.00, 'Dark');

I am trying to write a query to INSERT a new rows in this table. I want it to work in the next way: imagine it as 2 pair of rows, first - the rows where NDM is MIN (in my case NDM = 1, 3 rows), second pair - the rows where NDM is MAX (in my case NDM = 3, 1 row). Then I want to check if there are rows in first pair which KTOV is not presented in the second pair (in my case these are two rows with KTOV = 101, and KTOV = 103). Then I want to INSERT these two rows in the table copying all their values from the first pair, except NDM it should take the MAX NDM.

I tried something like

INSERT DMS(KTOV, NDM, KOL, CENA, SORT)
SELECT KTOV, a.NDM as NDM, KOL, CENA, SORT
FROM 
(SELECT 
MAX(NDM) AS NDM,
KTOV AS KTOV,
KOL AS KOL,
CENA AS CENA,
SORT AS SORT
FROM DMS) a
WHERE // I'm not sure how to continue;

As the result of the execution, it should add two new rows like (because '102' KTOV is presented in the rows with MAX NDM)

INSERT INTO DMS VALUES('101', 3, 20, 10.00, 'Light');
INSERT INTO DMS VALUES('103', 3, 30, 5.00, 'Dark');

P.S: I am going to use it as a REORDER functionality

CodePudding user response：

You could do it like this:

with mx as 
  (select * from DMS where NDM in (select max(NDM) from DMS)),
  mn as
  (select * from DMS where NDM in (select min(NDM) from DMS))
  insert into dms (KTOV,NDM,KOL,CENA,SORT)
select KTOV,NDM,KOL,CENA,SORT from mx where not exists (select * from mn where mn.KTOV = mx.KTOV)
union 
select KTOV,(select max(NDM) from DMS),KOL,CENA,SORT from mn where not exists (select * from mx where mn.KTOV = mx.KTOV);

DBFiddle demo