• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

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

0
Geoff Millikan
Asked:
Geoff Millikan
  • 7
  • 3
1 Solution
 
HainKurtSr. System AnalystCommented:
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?
0
 
HainKurtSr. System AnalystCommented:
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"?

0
 
HainKurtSr. System AnalystCommented:
looks like you should use these col names : insert_year,insert_month,the_count

one sample is :

ON DUPLICATE KEY UPDATE
  insert_year=values(insert_year),
  insert_month=values(insert_month),
  the_count=-values(the_count)

make count negative in case of any duplicate ;)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HainKurtSr. System AnalystCommented:
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?
0
 
Geoff MillikanAuthor Commented:
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

0
 
HainKurtSr. System AnalystCommented:
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...
0
 
Geoff MillikanAuthor Commented:
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/
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
HainKurtSr. System AnalystCommented:
ooo I got it now, in case of duplicate you want to update the count column...
0
 
Geoff MillikanAuthor Commented:
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?
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now