Re: mysql update/replace syntax

From: Westcoast Sheri (sheri_deb88_at_nospamun8nospam.com)
Date: 08/30/04 

Date: Mon, 30 Aug 2004 12:40:02 GMT

Gordon Burditt wrote:

> >> > You are approching this from the wrong angle. You should be quering the
> >> > customer sales records and producing summary information from that.
>
> This is a hospital kitchen. Recording Personally Identifiable
> Information about a patient in a database makes that database legally
> unusable for the purpose intended for it (what kind of food they
> should order and how much they should keep on hand), and that's why
> your predecessor is in jail now: violating HIPAA requirements.
>
> >> > INSERT INTO sales (customer, product , qty) VALUES
> >> > ('$customer','$fruit','$qty')
> >> >
> >> > SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
> >> > numsales
> >> >
> >> > http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
> >>
> >> No, I am wanting to *record* what visitor does. If they buy 5 apples, I
> >want
> >> mySQL to find the row with "apple" in it, and increment it by 5. If there
> >is
> >> *no* row with "apple", then create a row and enter a "5" there.
>
> It is possible to insert or update a row with one query:
>
> INSERT INTO sales_summary SET product = 'apple', qty = 3
> ON DUPLICATE KEY UPDATE qty = qty + 3;

....actually this is the first code I ever tried (because it's exactly what I
wanted in the first place!), and kept getting errors! But then I realized that
perhaps my server was not using the mySQL version that allowed this ("...blah
blah blah syntax error near "ON DUPLICATE KEY"). That is the perfect line of
code, though! Thanks!!!