[RESOLVED] update or insert

lajkonik86

i have count i need to increment.
the count is kept for both date and area.
so whenever it need to be incremented i don't know wheter there is already an row keeping track of the current day and area.

when there is one i need an update and when there's none i need an insert
i was thinking of doing this with mysql affected rows thingie, but im sure there must be a better way amongst the pros here 🙂

lajkonik86

the reason why im asking is cause i also have a little harder situation. Where the page receives multiple items. The score needs to be incremented for all of them. And naturally i can take care of them all at once with the update statement. However i have no clue then how to track which ones didn't make it into the database.
Any tips on that?

rincewind456

You could use insert for all the rows along with the ON DUPLICATE KEY option for any that just need updating.

lajkonik86

that would still require 8 queries for 8 items :S

lajkonik86

suppose i could first run an update statement, than if the number of affected rows is lower than the nummer of items needed, check which ones aren't in the database and insert for those. This would leave me with under a successful scenario 1 query and under the bad one 2+number of items

liquorvicar

You can use INSERT...ON DUPLICATE KEY with multiple values.
From the MySQL manual:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;

Would that not work?

lajkonik86

i was not aware of that thanks.
Dont think i can use it though.

cause the combination of 3 fields is unique (company,area,popularity)
so i dont see how i could use to ON DUPLICATE KEY statement
please explain

liquorvicar

Maybe you need to explain what you're trying to do in a bit more detail? Maybe post your table structure, and a few example rows before and after an update.

lajkonik86

Example Rows (Keeping Regions constant for simplicity)

id   companyid 	region 	prints  	      day
6	    5 	      4000     7 	          11666160000
7	    4 	      4000     8 	          11166702400

Example Todo:
for company 4&5 increment the prints field on day 11166702400

In this example i would need an update for company 4 and an insert for company 5 (since it does not have a row storing the amount of prints for the current date.)

liquorvicar

Yes this is possible with the example above.

First, define a multi-column unique index on companyid and day (and regionid if needed).

Then

INSERT INTO table (companyid,region,prints,day) VALUES (4,4000,1,11166702400), (5,4000,1,11166702400)  ON DUPLICATE KEY UPDATE prints=prints+1

I believe that should achieve the desired result.

btw, you might decide not to have an ID field and make the multi-column index the primary key...

lajkonik86

nice🙂
ill search the manual on how that multi column index works.
Thanks for the great help!

lajkonik86

SQL query:

INSERT INTO `csite_popularity` ( `id` , `couponid` , `companyid` , `region` , `prints` , `day` )
VALUES ( 1, 3, 5, 4000, 1, 11166702400 )

MySQL said: Documentation
#1062 - Duplicate entry '3-4000-11166702400' for key 1

Then when i add the code according to the manual

SQL query:

INSERT INTO `csite_popularity` ( `id` , `couponid` , `companyid` , `region` , `prints` , `day` )
VALUES (
NULL , 4, 1, 4000, 1, 11166702400
), (
NULL , 3, 5, 4000, 1, 11166702400
) ON DUPLICATE
KEY UPDATE prints = prints +1

MySQL said: Documentation
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE prints=prints+1' at line 3

Im confused

liquorvicar

What version MySQL are you using? ON DUPLICATE KEY is only available from v4.1

lajkonik86

hmm looks like im on the edge
MySQL client version: 4.1.10

liquorvicar

It's the SERVER version not the CLIENT version that's important.