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

Geoff MillikanAsked:
Who is Participating?
 
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.