Re: mysql update/replace syntax

From: CJ Llewellyn (satest_at_tmslifeline.com)
Date: 08/29/04 

Date: Sun, 29 Aug 2004 17:46:30 +0100

On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:

> CJ Llewellyn wrote:
>
>> "Westcoast Sheri" <sheri_deb88@nospamun8nospam.com> wrote in message
>> news:4131C87F.5BF798C7@nospamun8nospam.com...
>> > CJ Llewellyn wrote:
>> >
>> > > "Westcoast Sheri" <sheri_deb88@nospamun8nospam.com> wrote in message
>> > > news:41315FCC.AFFCF91D@nospamun8nospam.com...
>> > > > To keep track of how many fruits my visitors buy, I use a mySQL
>> database
>> > > > (2 columns: "fruit" and "quantity")....so can we make these following
>> > > > mySQL queries work somehow?
>> > > >
>> > > > (visitor buys 5 apples):
>> > > > replace into fruit_database set fruit = 'apple' , quantity = quantity
>> +
>> > > > 5;
>> > > >
>> > > > (visitor buys 7 apples):
>> > > > replace into fruit_database set fruit = 'apple' , quantity = quantity
>> +
>> > > > 7;
>> > > >
>> > > > (visitor buys 1 grape):
>> > > > replace into fruit_database set fruit = 'grape' , quantity = quantity
>> +
>> > > > 1
>> > >
>> > > You are approching this from the wrong angle. You should be quering the
>> > > customer sales records and producing summary information from that.
>> > >
>> > > 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.
>>
>> Yes you are. The above will tell you exactly how many apples or bananas have
>> been sold without resorting to increamenting a seperate table/record.
>>
>> Further more, with a little creativity, you can find out things like, on
>> what day which fruit sells best. How much of each fruit do you sell each
>> month? Is there a peek period for selling oranges?
>
> no no no. If visitor "a" buys 5 apples, then a "5" should be in the mySQL
> table. But then, an hour later, if visitor "b" buys 3 more apples, then an "8"
> should be in the mySQL table. What you are suggesting is that first there will
> be a "5" in the table....then when visitor "b" buys 3 apples, there will then
> be a "3" in the table. I really thought I worded my question very well.
> Apparantly not. Sorry.

You question was understood. It's just your approach to the problem is
wrong.

If you have a table that holds

Apple 5
Orange 6
Banana 567

This will not tell you a lot. Only in total how much of each fruit you've
sold. Unless you reset the values every week, you'll not be able to spot
trends in people's buying thus purchasing more stock than is needed.

You have a table holding

Fred Smith 1/1/04 Apple 2
Fred Smith 1/1/04 Banana 3
John Jones 2/1/04 Apple 5

You can tell how many apples and bananas have been sold, by summing them
up.
 

Relevant Pages

  • Re: Black seedless grapes
    ... clementines, bananas, strawberries, apples and pineapple. ... The pieces of fruit were large, with the exception of the apples, I cut those up way too small. ... We had this after dinner and the dressing had firmed on the fruit and it was perfect, but I forgot to add the toasted nuts. ...
    (rec.food.cooking)
  • Re: quick foods
    ... fruit (berries of all kinds, melons & bananas - since I'm allergic to apples, plums and peaches) ... cottage cheese & yoghurt ... What kinds of foods do you like to keep around as quick and easy, ...
    (alt.med.fibromyalgia)
  • Re: PART II
    ... two kinds of fruit in the result set i.e. 345. ... > SELECT UserID FROM Users ... >> How do I get users who have only Apples and Bananas. ...
    (microsoft.public.sqlserver.programming)
  • What is the best way to load this tree (c# & sql question)
    ... Say you have three tables apples, oranges and bananas and each table has a ... bananas) And second it won't work because I don't have a sql statement ... unions but due to the unequal amount of fields it's not working out for me. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Clue bats
    ... You have a bowl of mixed fruit. ... I make a comment about apples, Catja ... that bananas aren't apples is not a reply about apples. ...
    (rec.arts.sf.composition)