How to update and copy a row in SQL?

Let's say I have a row in a ProductTable. That row's ItemID column links to ItemTable's ID column. The ID columns of both tables are primary keys and identity columns.

Like this:

ProductTable:

ID    ItemID   Value   ValueOther   Latest   Time
12    100      'foo'   'bar'        1        <Autogenerated timestamp>


ItemTable:

ID    Col      ColOther    Time
100   'old'    'oldother'  <Autogenerated timestamp>

Then I will manually INSERT a new row to the ItemTable:

query = \
    """
    INSERT INTO ItemTable (Col, ColOther)
    VALUES (?, ?);
    """
cursor.execute(query, 'new', 'newother')

This inserted row gets the next available ID, say 250.

 

Then I want to automatically UPDATE the original row in the ProductTable to have Latest = 0.
And automatically INSERT the same row with the new ItemID and Latest = 1.

This automatic INSERT and UPDATE would preferably happen with just a pure SQL query, but alternatively it could be implemented with some Python code. How would I go about doing this?

The wanted end result:

ProductTable:

ID    ItemID   Value   ValueOther   Latest   Time
12    100      'foo'   'bar'        0        <Autogenerated timestamp>
110   250      'foo'   'bar'        1        <Autogenerated timestamp>


ItemTable:

ID    Col      ColOther     Time
100   'old'    'oldother'   <Autogenerated timestamp>
250   'new'    'newother'   <Autogenerated timestamp>

from How to update and copy a row in SQL?