1

i have 3 tables (sample),no key restrictions
one is FRUITTABLE, second is FRUITPRICE, third is COMBINATIONS
in FRUITTABLE, we insert what is being sold, color(ie,banana green or yellow),taste,
in FRUITPRICE, we insert how many piece, if applicable pack and the time it was sold

this is how i create combinations

SELECT FT.FRUITS,  FT.COLOR, FT.TASTE, COUNT(FP.SALES) AS TOTAL, FP.TIMESOLD
FROM FRUITSTABLE FT
JOIN FRUTSPRICE FP  ON FT.ID = FP.ID
WHERE FP.TIMESOLD BETWEEN '2013-12-01 11:00:00' AND '2013-12-01 12:00:00'
GROUP BY FT.FRUITS, FT.COLOR, FT.TASTE

in the COMBINATIONS table, what we do is we group it and count so we will see what is most likely good fruit combination per hour

SO COMBINATIONS WILL OCCUR ONCE EVERY HOUR

lets say

ie: mango,yellow,sour,10, 3:00PM
ie: mango,yellow,sour,12, 4:00PM
ie: mango,yellow,sour,14, 5:00PM
ie: mango,yellow,sour,10, 6:00PM

so evey hour, lets say 3:00PM
we insert
mango,yellow,sour,1, 3:00PM
then another customer bought this combination at the same hour so the data will be
mango,yellow,sour,2, 3:00PM

now, in combinations, we insert it. but if the combination already exist, i honestly dont know how i can update it.. we need to update it lets say every 5min, maybe i can manage to create SP and EVENT that will call SP(hoping this is correct)

the only problem is i dont know how to: select, insert, if exist(the combinations of FT.FRUITS, FT.COLOR, FT.TASTE are same) update
pls let me know if what im thinking is possible or not..
thanks in advance

PS. i already used insert on duplicate key update in some cases
PS. we determine the group combination and total sales(FP.SALES) per hour(TIMESOLD)

EDIT

PS replace into is not applicable as well
also the reason why i cant use a unique key is because it will be useless..
because each combination will occur every hour.. honestly,
i already have solution. but it wont be updated every minute,
my solution will be insert it every hour.
the negative side of doing that is the record in the webpage will not be in real time
all i need to figure out is how i can do something LIKE
insert on duplicate key update (but without using primary key)
so that the table will be updated and the record will be in real time if its possible to create a workaround

sorry if i have to edit the question many times. i hope this one is constructive.. thank you for your help guys...

xten
  • 36
  • 4
  • 1
    Why are you not allowed to use a primary key? – Explosion Pills Sep 20 '13 at 16:11
  • One of the principles of normalization is to not store calculated values. That being the case, why do you even need the combinations table? – Dan Bracuk Sep 20 '13 at 16:13
  • Hi, this is just a sample, the reason is that, all combination is allowed, what is mostly bought per hour, day and month.. so the combination will occur once every hour – xten Sep 20 '13 at 16:19
  • i removed ID in group by, thanks – xten Sep 20 '13 at 16:24
  • You still haven't explained why you can't create a unique index? If you're not allowed to repeat a combination, create a unique index on that composite key. – Barmar Sep 20 '13 at 16:35
  • Without a unique index, it's not possible to do what you want in a single query. You need one query to SELECT the row to see if it exists, then a second to UPDATE or INSERT. You can do this with a SP, or with code in a client language. To be totally safe you need to use a lock or transactions, to prevent a change to the table between the SELECT and INSERT. – Barmar Sep 20 '13 at 16:37
  • hi, i cant create unique key because it will be useless.. because each combination will occur every hour.. honestly, i already have solution. but it wont be updated every minute, my solution will be insert it every hour. the negative side of doing that is the record in the webpage will not be in real time – xten Sep 20 '13 at 16:44
  • hi, pls see the question again.. i change some.. sorry my bad – xten Sep 20 '13 at 16:52
  • sorry for not being constructive, i have updated the question – xten Sep 20 '13 at 17:28

3 Answers3

1

You are probably looking for this :

Insert to table or update if exists (MySQL)

OR:

You can actually make the combination (of FT.FRUITS, FT.COLOR, FT.TASTE) a key such that they can individually have multiple values but there will be unique combinations of them in the table.

Community
  • 1
  • 1
Wajahat
  • 1,593
  • 3
  • 20
  • 47
  • hi Wajahat, thanks for the answer but i cant use it because there are no unique keys in the table and we are not allowed to add unique key – xten Sep 20 '13 at 16:25
  • Then you probably will have to use some kind of If else structure in the SQL statements, I don't exactly know how that is done. But theoretically speaking, the key (primary, secondary, candidate) can be a single attribute or a combination of attributes and in your case I see the combination of 3 attributes as a candidate key, but you say you are not allowed to use that; that is bad. – Wajahat Sep 20 '13 at 16:31
  • ^^, yes, it is headache – xten Sep 20 '13 at 16:45
0

Creating a Compound Key http://en.wikipedia.org/wiki/Compound_key would be the right solution. However, if you are not able to create keys for whatever reason, do it the other way round:

UPDATE myTable SET [whatever goes here] WHERE FRUITS = [currentFruit] AND COLOR = [currentColor] AND TASTE = [currentTaste]

Now, use your programing language and retrieve the affected row count. If it's 1 - you're done. If it's 0: You need to insert, because there was no row matching your update statement.

dognose
  • 20,360
  • 9
  • 61
  • 107
  • hi dognose, i just updated my question.. i will look on compound_key..hope it works, thanks – xten Sep 20 '13 at 17:29
0

Just in case, if you are using load data statement instead of insert(like reading from csv or customized delimited file). You can use the replace flag.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

B.Mr.W.
  • 18,910
  • 35
  • 114
  • 178