CODEWITHSUNDEEP
mysqlsql
xten
xten

Reputation: 36

how to insert if exist update (no unique key)

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...

Upvotes: 1

Views: 2153

Answers (3)

B.Mr.W.
B.Mr.W.

Reputation: 19628

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

Upvotes: 0

dognose
dognose

Reputation: 20899

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.

Upvotes: 0

Wajahat
Wajahat

Reputation: 1633

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.

Upvotes: 1

Related Questions