Classify incoming identities before a proposed synchronization

For each incoming itemid, report action=update when targetitems already has that identity and action=insert otherwise. Do not write any rows. Target rows absent from the incoming batch are outside this report, not candidates for deletion.

MySQL dialect.

sql
CREATE TABLE target_items (item_id INT PRIMARY KEY,label VARCHAR(40) NOT NULL); CREATE TABLE incoming (item_id INT PRIMARY KEY,label VARCHAR(40) NOT NULL);
INSERT INTO target_items VALUES (1,'Old'),(2,'Keep'); INSERT INTO incoming VALUES (1,'New'),(3,'Add');

Required ordering: Ascending incoming item_id.

Edge cases:

• An empty target classifies every supplied input as insert.
• Empty incoming input produces no action row.