JesusTheHun - 4 months ago 29

MySQL Question

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`

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`

`amount`

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";`

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

Of course I could alter the table and add a useless

`amount`

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.