By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (
field1 VARCHAR(255),
field2 VARCHAR(255),
apporder INT
);
CREATE TABLE YourTable2 (
field1 VARCHAR(255),
field2 VARCHAR(255),
apporder INT
);
INSERT INTO YourTable (field1, field2, apporder)
VALUES
('Data 1', 'Value 1', 1),
('Data 2', 'Value 2', 2),
('Data 3', 'Value 3', 3),
('Data 4', 'Value 4', 4);
4 rows affected
INSERT INTO YourTable2 (field1, field2, apporder)
VALUES
('Data 1', 'Value 1', 10),
('Data 2', 'Value 2', 22),
('Data 6', 'Value 3', 35),
('Data 5', 'Value 4', 42);
4 rows affected
MERGE INTO YourTable2 AS tgt
USING (
SELECT field1, field2, MAX(apporder) AS maxAppOrder
FROM YourTable
GROUP BY field1, field2
) AS src ON tgt.field1 = src.field1 AND tgt.field2 = src.field2
WHEN NOT MATCHED THEN
INSERT (field1, field2, apporder)
VALUES (src.field1, src.field2, src.maxAppOrder + 1);
2 rows affected
SELECT * FROM YourTable
SELECT * FROM YourTable2
| field1 | field2 | apporder |
|---|---|---|
| Data 1 | Value 1 | 1 |
| Data 2 | Value 2 | 2 |
| Data 3 | Value 3 | 3 |
| Data 4 | Value 4 | 4 |
| field1 | field2 | apporder |
|---|---|---|
| Data 1 | Value 1 | 10 |
| Data 2 | Value 2 | 22 |
| Data 6 | Value 3 | 35 |
| Data 5 | Value 4 | 42 |
| Data 3 | Value 3 | 4 |
| Data 4 | Value 4 | 5 |