1

I am making a daily counter for a blog and I have problem with this query :

IF EXISTS SELECT * FROM tableA WHERE blog_id=1
    UPDATE FROM tableA SET c=c+'1' WHERE blog_id='1' AND c_date=NOW()
ELSE
    INSERT INTO FROM tableA VALUES (blog_id,c,c_date) VALUES (1,1,now())

its showing following error :

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 'IF EXISTS SELECT * FROM tableA WHERE blog_id=1 UPDATE FROM tableA SET c=c+'' at line 1

My table entries should look like this :
id , blod_id , c , c_date
1 , 1001, 66 , 2011-11-11 
2 , 1001, 160 , 2011-11-12
3 , 1002, 200 , 2011-11-12
4 , 1003, 33 , 2011-11-12
user82324
  • 79
  • 2
  • 9

2 Answers2

3

I suspect you're lokoing for the insert into on duplicate key update syntax

From the manual:

12.2.5.3. INSERT ... ON DUPLICATE KEY UPDATE Syntax

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Your query should look more like the following example:

INSERT INTO tableA (blog_id,c,c_date) VALUES (1,1,now()) ON DUPLICATE KEY UPDATE `c` = `c`+1, `c_date` = NOW()

You need to have a UNIQUE key or a PRIMARY KEY for this to work.

ALTER TABLE  `table` ADD PRIMARY KEY (  `id` )

Based on your table example you could add a primary key to the id column or add a unique composite index on the (c_date, blog_id) fields

ALTER TABLE  `table` ADD UNIQUE (
blog_id ,
c_date
);

and use this:

INSERT INTO tableA (blog_id, c, c_date) 
  VALUES (1, 1, NOW()) 
ON DUPLICATE KEY UPDATE 
  c = c + 1                --- update only `c`, not any part of the unique key
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Ben Swinburne
  • 25,669
  • 10
  • 69
  • 108
  • Thanks but your query is showing same error as mine : #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 'VALUES (1,1,now()) ON DUPLICATE KEY UPDATE `c` = `c`+1, `c_date` = NOW()' at line 1 – user82324 Nov 12 '11 at 15:35
  • I fixed the error but I have new problem . its inserting multiple records instead of updating it ... – user82324 Nov 12 '11 at 15:40
  • Do you have a unique index on the table? You must have a unique field on the row, be it a `UNIQUE` index or a `PRIMARY KEY`. When you try and insert something which is a duplicate of the unique one it will update, otherwise it'll insert. – Ben Swinburne Nov 12 '11 at 16:27
  • no I don't have , but neither blog_id and nor c_date are not unique , if u see that sample table that i added to the question you can see counter is based on the day , mean each blog_id has a daily counter which is used for reports ... – user82324 Nov 12 '11 at 19:52
1

It seems to me what you really want is this (beware, it only works with MySQL)!

MatTheCat
  • 18,071
  • 6
  • 54
  • 69
  • thanks but how can I use where in there ? I have edited the question and you can see a sample table which I am going to have it . – user82324 Nov 12 '11 at 15:43
  • Ben Swinburne's answer is ok but you have to put a unique index on blog_id (so I think your sample is wrong as you have 1001 twice) – MatTheCat Nov 12 '11 at 15:48
  • blog_id 1001 has different counter_date ... counter_date will be used in getting reports ... – user82324 Nov 12 '11 at 15:53
  • Right I didn't notice. You have to put an unique index on blog_id AND c_date: http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – MatTheCat Nov 12 '11 at 15:55
  • it doesn't work , as I said to Ben neither blog_id no c_date are not unique , I tried to make these two fields unique but it didn't work , after following ur suggestion its showing this error after third query : #1062 - Duplicate entry '1-2' for key 'blog_id' – user82324 Nov 12 '11 at 19:57