How to transfer data from one MySql table to another, while updating in the first one?

Question

MySQL

Hello comrades! Once again, your help is needed!
There are 2 almost identical tables:

CREATE TABLE `queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `count` tinyint(3) unsigned NOT NULL,
  `time` int(11) unsigned NOT NULL,
  `state` enum('draft','pending','accepted') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_product_id` (`user_id`,`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20267 DEFAULT CHARSET=utf8;

and

CREATE TABLE `offers` (
  `user_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `count` tinyint(3) unsigned NOT NULL,
  `finish_time` int(11) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`product_id`),
  UNIQUE KEY `user_id_product_id` (`user_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Records are created in the queue, which will have to be transferred to the offers table by a script or a button, if they have state= 'pending' and if successful, the status in the queue will change to accepted.
Currently using the following solution on yii2:

public static function executeQueue(array $keys = [])
  {
    $returnArray = [];
    if (count($keys) > 0) {
      $rows = Queue::find()->where([
        'state' => Queue::STATE_PENDING,
        'id' => $keys
      ])->all();
    } else {
      $rows = Queue::findAll(['state' => Queue::STATE_PENDING]);
    }
    foreach ($rows as $specialOffer) {
      $record = new Offer();
      $record->user_id = $specialOffer->user_id;
      $record->product_id = $specialOffer->product_id;
      $record->count = $specialOffer->count;
      $record->finish_time = $specialOffer->time;
      if ($record->validate() && $record->save()) {
        $specialOffer->state = Queue::STATE_ACCEPTED;
        $specialOffer->save();
        $returnArray[] = $specialOffer->id;
      }
    }
    return $returnArray;
  }

Which generally handles hundreds of records, but needs to handle thousands!
The simplest solution is to rewrite it in Sql.
It was easy to transfer data from one table to another:

INSERT INTO offers (
  user_id,
  product_id,
  count,
  finish_time
) SELECT
  user_id,
  product_id,
  count,
  time
FROM
  queue
WHERE
  state = 'pending'

But how to change the state pending->accepted for all the records touched by the request?
Moreover, the question of conflicts remains, because theoretically offers may already have an entry with a given user_id, product_id (UNIQUE KEY `user_id_product_id` (`user_id`,`product_id`))
Please guide me on the right path and suggest how to implement the request (or even procedure)?

Answer 1

1) Transfer data:

INSERT DELAYED IGNORE INTO offers (
  user_id,
  product_id,
  count,
  finish_time
) SELECT
  user_id,
  product_id,
  count,
  time
FROM
  queue
WHERE
  state = 'pending'

2) Change statuses:

UPDATE queue
SET state = 'accepted'
WHERE
  state != 'accepted'
AND (product_id, user_id) IN (
  SELECT
    product_id,
    user_id
  FROM
    offers
)

Answer 2

Use
select * from table for update;
update table ....;