asked on

MySQL Error on INSERT... SELECT... ON DUPLICATE KEY UPDATE...

Using the query below I get this error on MySQL 5.0.77. What do I need to do to make the query work?

Unknown column 'p.insert_dttm' in 'field list'

Seems like the query should work per:
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Thanks,

http://www.t1shopper.com/

---== Query ==---
INSERT INTO
  counters.tools_ports_scanned_count (insert_year,insert_month,the_count)
  SELECT
    CONVERT(YEAR(insert_dttm),char(4)),
    MONTH(insert_dttm),
    SUM(port_count)
  FROM
    network_tools.portscan p
  WHERE
    error_condition='NO_ERROR'
    AND port_count<500
    AND YEAR(insert_dttm)=YEAR(NOW())
    AND MONTH(insert_dttm)=MONTH(NOW())
ON DUPLICATE KEY UPDATE
  insert_year=CONVERT(YEAR(p.insert_dttm),char(4)),
  insert_month=MONTH(p.insert_dttm),
  the_count=SUM(p.port_count)

Open in new window

HainKurt

it says:

In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables, as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify nonunique column names in the values part.

is that true for your query?

HainKurt

i am trying to understand the query,

but "SELECT" and "ON DUPLICATE KEY UPDATE" is exactly same...

so why are you using "ON DUPLICATE KEY UPDATE"?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

I agree, 26175374 is not a good example ;)

what are you trying to do if the inserted record creates a duplicate? how do you want to resolve the issue?

Geoff Millikan

ASKER

I figured it out! On the "ON DUPLICATE KEY UPDATE" part I need to do this:
...
ON DUPLICATE KEY UPDATE
insert_year=VALUES(insert_year),
insert_month=VALUES(insert_month),
the_count=VALUES(the_count)

It was challenging to figure out because there's no examples in the manual but here's what it says:

"You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred."

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Final query below.

INSERT INTO
  counters.tools_ports_scanned_count (insert_year,insert_month,the_count)
  SELECT
    YEAR(NOW()),
    MONTH(NOW()),
    SUM(p.port_count)
  FROM
    network_tools.portscan p
  WHERE
    p.error_condition='NO_ERROR'
    AND p.port_count<500
    AND YEAR(p.insert_dttm)=YEAR(NOW())
    AND MONTH(p.insert_dttm)=MONTH(NOW())
ON DUPLICATE KEY UPDATE
  the_count=VALUES(the_count)

Open in new window

HainKurt

looks like ON DUPLICATE KEY UPDATE part you can only use constants, column names of inserted table, and values(col_name) for the current value of the column to be inserted...

Geoff Millikan

ASKER

Ah, the target table has a UNIQUE constraint on the insert_year,insert_month columns. This table gives usage by month and the table is refreshed every 10 mins or so to display hit counters.

Example:
http://www.t1shopper.com/tools/port-scan/

HainKurt

above query does not do anything ;)
you say, insert this record, if it creates a duplicate, make the_count=VALUES(the_count) and insert again ;)

it will not change anything and will give duplicate error again...

HainKurt

ooo I got it now, in case of duplicate you want to update the count column...

Geoff Millikan

ASKER

It works because the VALUES(the_count) is the NEW value! What would have been inserted had their not been a collision on the UNIQUE key.

So the column "the_count" is updated with the new value.

What's weird is that when I run the query above, I get "2 rows affected by the last command" however only one row should be affected so that's really weird.

Thoughts?