How to use INSERT … SELECT with a particular colum

Question

I am using INSERT ... SELECT to insert a data from specific columns from specific rows from a view into a table. Here's the target table:

CREATE TABLE IF NOT EXISTS `queue` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `customerId` int(11) NOT NULL,
  `productId` int(11) NOT NULL,
  `priority` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `customerId` (`customerId`),
  KEY `productId` (`productId`),
  KEY `priority` (`priority`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

The INSERT ... SELECT SQL I have works, but I would like to improve it if possible, as follows: I would like the inserted rows to start with 1 in the priority column, and each subsequent row to increment the priority value by 1. So, if three rows were inserted, the first would be priority 1, the second 2, and the third 3.

A exception to the "start at 1" rule: if there are existing rows in the target table for the specified customer, I would like the inserted rows to start with MAX(priority)+1 for that customer.

I thought I could use a subquery, but here's the problem: sometimes the subquery returns NULL (when there are no records in the queue table for the specified customer), which breaks the insert, as the priority column does not allow nulls.

I tried to CAST the column to an integer, but that still gave me NULL back when there are no records with that customer ID in the table.

I've hardcoded the customer ID in this example, but naturally in my application that would be an input parameter.

INSERT INTO `queue`
(
`customerId`,
`productId`,
`priority`,
`status`,
`orderId`)
SELECT
    123, -- This is the customer ID
    `PRODUCT_NO`,
    (SELECT (MAX(`priority`)+1) FROM `queue` WHERE `customerId` = 123),
    'queued',
    null
FROM
    `queue_eligible_products_view` 

Is there a way to do this in one SQL statement, or a small number of SQL statements, i.e., less than SQL statement per row?

I do not think I can set the priority column to auto_increment, as this column is not necessarily unique, and the auto_increment attribute is used to generate a unique identity for new rows.

Answer 1

As Barmar mentions in the comments : use IFNULL to handle your sub query returning null. Hence:

INSERT INTO `queue`
(
`customerId`,
`productId`,
`priority`,
`status`,
`orderId`)
SELECT
    123, -- This is the customer ID
    `PRODUCT_NO`,
    IFNULL((SELECT (MAX(`priority`)+1) FROM `queue` WHERE `customerId` = 123),1),
    'queued',
    null
FROM
    `queue_eligible_products_view`

Answer 2

Here's how to do the incrementing:

INSERT INTO queue (customerId, productId, priority, status, orderId)
SELECT 123, product_no, @priority := @priority + 1, 'queued', null
FROM queue_eligible_products_view
JOIN (SELECT @priority := IFNULL(MAX(priority), 0)
      FROM queue
      WHERE customerId = 123) var