you can use this query

DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));
DELETE FROM wp_posts WHERE post_type IN ('product','product_variation');

Maybe I’m overthinking it, I’m not sure, but the solution below works for your test data and is somewhat understandable (to me at least).

The idea behind it is to add a root ID to all ID pairs using a recursive query. Then we use window functions partitioned by roots to find out the latest ID.

with recursive
  --
  -- Test data
  --
  changes(old_id, new_id) as (values
    (1,2),
    (2,3),
    (3,4),
    (999,1000)
  ),

  --
  -- Obtain all root IDs, i.e., all `old_id` values that don't appear in the
  -- `new_id` column.
  --
  roots(root_id) as (
    select old_id from changes where old_id not in (select new_id from changes)
  ),

  --
  -- Add a root ID to all pairs.
  --
  rooted_changes(root_id, old_id, new_id) as (
    select old_id, old_id, new_id
    from changes
    join roots on changes.old_id = roots.root_id

    union all

    select older.root_id, newer.old_id, newer.new_id
    from rooted_changes older
    join changes newer on older.new_id = newer.old_id
  ),

  --
  -- Partition each row over the root ID that it belongs too and extract
  -- the max `new_id` value (alternatives in terms of `first_value` and
  -- `last_value` are given).
  --
  result as (
    select
      old_id,
      max(new_id) over roots,

      -- alternatively
      first_value(new_id) over (roots order by new_id desc),

      -- alternatively
      last_value(new_id) over (roots
        order by new_id asc
        rows between current row and unbounded following
      )
    from rooted_changes
    window roots as (partition by root_id)
  )

-- Uncomment selectively to debug various stages:

-- select * from roots

-- select * from rooted_changes order by root_id asc

select * from result order by old_id asc;

Final result:

┌────────┬──────┬─────────────┬────────────┐
│ old_id │ max  │ first_value │ last_value │
├────────┼──────┼─────────────┼────────────┤
│      1 │    4 │           4 │          4 │
│      2 │    4 │           4 │          4 │
│      3 │    4 │           4 │          4 │
│    999 │ 1000 │        1000 │       1000 │
└────────┴──────┴─────────────┴────────────┘

Intermediate result of select * from roots:

┌─────────┐
│ root_id │
├─────────┤
│       1 │
│     999 │
└─────────┘

Intermediate result of select * from rooted_changes order by root_id asc:

┌─────────┬────────┬────────┐
│ root_id │ old_id │ new_id │
├─────────┼────────┼────────┤
│       1 │      1 │      2 │
│       1 │      2 │      3 │
│       1 │      3 │      4 │
│     999 │    999 │   1000 │
└─────────┴────────┴────────┘

Do you need in this:

SELECT [serialno],
       [storedatetime], 
       MAX(CASE WHEN [stepid]= 50 THEN {some_column_name} END) [value1],
       MAX(CASE WHEN [stepid]= 80 THEN {some_column_name} END) [value2],
       ROW_NUMBER() OVER (PARTITION BY [serialno] ORDER BY [storedatetime] desc) AS RowNumber 
FROM [database] 
WHERE [processid] = 610 and [stepid] IN (50, 80)
GROUP BY 1, 2