How to select id's from one table and insert/update to another table

Question

I am working on an automated trading system in python, I have set up a database which inserts a row every time a new order is generated.

What I am having trouble with is if the order is successful I would like to add the OrderID to my Position Table.

Here are the scenarios that could happen

if there is no open reference with the same OrderID, AccountID, strategyID, AssetID then the insert new row where PositionID=NUM, OpenReference=OrderID, CloseReference=NULL, Status=2

if there are positions that are not of status(3) existing check to see if it matches the OpenReference parameters(OrderID, AccountID, strategyID, AssetID) if it does update the OrderID to column CloseReference and update status=3.

Order Table Setup

CREATE TABLE `__order` (
  `OrderID` int NOT NULL AUTO_INCREMENT,
  `AccountID` int DEFAULT NULL,
  `StrategyID` int DEFAULT NULL,
  `AssetID` int DEFAULT NULL,
  `TimeSubmitted` datetime DEFAULT NULL,
  `Action` mediumtext,
  `Type` mediumtext,
  `Price` float DEFAULT NULL,
  `Quantity` int DEFAULT NULL,
  `Outstanding` int DEFAULT NULL,
  `TimeCompleted` datetime DEFAULT NULL,
  `Commission` float DEFAULT NULL,
  `Status` mediumtext,
  PRIMARY KEY (`OrderID`),
  KEY `AssetID_FORK_idx` (`AssetID`),
  KEY `AccountID_FORK_idx` (`AccountID`),
  KEY `StratID_FORK_idx` (`StrategyID`),
  CONSTRAINT `AccountID_FORK` FOREIGN KEY (`AccountID`) REFERENCES `__account` (`AccountID`),
  CONSTRAINT `AssetID_FORK` FOREIGN KEY (`AssetID`) REFERENCES `__asset` (`AssetID`),
  CONSTRAINT `StratID_FORK` FOREIGN KEY (`StrategyID`) REFERENCES `__strategy` (`StrategyID`)
) ENGINE=InnoDB AUTO_INCREMENT=577 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Position Table Setup

CREATE TABLE `__position` (
  `PositionID` int NOT NULL AUTO_INCREMENT,
  `OpenReference` int DEFAULT NULL,
  `CloseReference` int DEFAULT NULL,
  `Status` int DEFAULT NULL,
  PRIMARY KEY (`PositionID`),
  KEY `BuyReference_order_FK_idx` (`OpenReference`),
  KEY `SellReference_order_FK_idx` (`CloseReference`),
  KEY `Status_order_FK_idx` (`Status`),
  CONSTRAINT `BuyReference_order_FK` FOREIGN KEY (`OpenReference`) REFERENCES `__order` (`OrderID`),
  CONSTRAINT `SellReference_order_FK` FOREIGN KEY (`CloseReference`) REFERENCES `__order` (`OrderID`),
  CONSTRAINT `Status_order_FK` FOREIGN KEY (`Status`) REFERENCES `__status` (`StatusID`)
)

My Python Code

def insert_position(self, openRef=None, status=None):
    return self.execute(
        sql="""
            INSERT INTO __position
                (OpenReference, Status) 
            VALUES 
                (%s, %s);""",
        params=(openRef, status,))

def update_position(self, positionID, closeRef=None, status=None):
    return self.execute(
        sql="""
            UPDATE __position
                SET CloseReference = %s,
                    Status = %s
            WHERE PositionID = %s;""", params=(closeRef, status, positionID,))

Answer 1

Start with a SELECT query to check if there are any positions with the given order ID.

If there are, update all the ones with status != 3 as required. If not, insert the new row.

def insert_or_update_position(self, order_id):
    self.execute(sql = "SELECT 1 FROM __position WHERE OpenReference = %s LIMIT 1", (order_id,))
    row = self.cursor.fetchone()
    if row:
        self.execute(sql="""
            UPDATE __position
            SET CloseReference = %s, Status = 3
            WHERE OpenReference = %s AND Status != 3""", (order_id, order_id))
    else:
        return self.execute(sql="""
            INSERT INTO __position (OpenReference, Status)
            VALUES (%s, 2)""", (order_id,))