JesusTheHun JesusTheHun - 6 days ago 4
MySQL Question

mysql on duplicate use non existing column

The SGBD is MySQL.
I have a table with a UNIQUE index on the column A.
On duplicate, I want to apply this rule :

nb_occurence = nb_occurence + 1,
average_amount = IF(average_amount IS NULL, VALUES(amount),
(average_amount * nb_occurence + VALUES(amount)) / (nb_occurence + 1)
),
min_amount = IF(VALUES(amount) < min_amount, VALUES(amount), min_amount),
max_amount = IF(VALUES(amount) > max_amount, VALUES(amount), max_amount)


The problem ?
amount
is not an existing column.
The data I insert : I have a very large set of data with a lot of collision on the column A (UNIQUE INDEX). I insert them all in the table, leaving to MySQL the calculations. In this set of data, each row has an
amount
column. I want MySQL to update
amount
relative columns by itself, using the data I try to insert.

I tried this :

INSERT INTO `myTable`(`A`, `amount`) VALUES('foobar', 5)

ON DUPLICATE KEY UPDATE
nb_occurence = nb_occurence + 1,
average_amount = IF(average_amount IS NULL, VALUES(amount),
(average_amount * nb_occurence + VALUES(amount)) / (nb_occurence + 1)
),
min_amount = IF(VALUES(amount) < min_amount, VALUES(amount), min_amount),
max_amount = IF(VALUES(amount) > max_amount, VALUES(amount), max_amount)


And it tells me :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'amount' in 'field list'
.

I tried a double quote around the field, like when you do a bare
SELECT "toto";
but SQL syntax error.

I bet there is a way to do that correctly in SQL !

Of course I could alter the table and add a useless
amount
column, but I hope there is a better way :)

EDIT : create table statement :

CREATE TABLE myTable
(
A VARCHAR(32) PRIMARY KEY NOT NULL,
nb_occurence INT(11) NOT NULL,
average_amount INT(11) NOT NULL,
min_amount INT(11) NOT NULL,
max_amount INT(11) NOT NULL
);

Answer

Try this:

INSERT INTO myTable (A, average_amount) VALUES('foobar', 5)
ON DUPLICATE KEY UPDATE 
    nb_occurence = nb_occurence + 1,
    average_amount = IF(average_amount IS NULL, VALUES(average_amount), 
        (average_amount * nb_occurence + VALUES(average_amount)) / (nb_occurence + 1)),
    min_amount = IF(VALUES(average_amount) < min_amount, VALUES(average_amount), min_amount),
    max_amount = IF(VALUES(average_amount) > max_amount, VALUES(average_amount), max_amount)

The problem is in your first line, when you are listing the columns you want to insert into. You reference "amount", meaning that you want to insert into the column amount, but that column doesn't exist in the myTable table. So naturally you get the error seen.