Page 1 of 1

REPLACE and increment

Posted: Fri May 05, 2006 10:02 am
by o11w
Hi,

I would like to increment a record into my table in one shot. So tried this query:

Code: Select all

REPLACE INTO mytable (season, n) VALUES (2005,n++);
( season is the primary key of the table )

(using MySQL)

But it doesn't work... the value of `n` stay at 1.

Do you have a solution?

Thanks mates.

Posted: Fri May 05, 2006 10:34 am
by feyd
++ isn't an operator in MySQL.

Try n+1

Posted: Fri May 05, 2006 11:04 am
by o11w
yes i'v tried this also.. but i doesn't work... :/

Posted: Fri May 05, 2006 11:08 am
by s.dot
Would a simple update work?

Code: Select all

UPDATE `table` SET `n`=`n`+1

Posted: Fri May 05, 2006 11:12 am
by o11w
I thought to use the REPLACE because if the records don't exist (where a new season occures), it will create the record.

Posted: Fri May 05, 2006 2:37 pm
by raghavan20
IF you have n + 1, I think if there is no row available, it will insert a row and 'n' will have the value one.

If there is a row, it will update 'n' as current 'n' value + 1.

Posted: Fri May 05, 2006 3:08 pm
by timvw
Read the manual for INSERT and look at the ON DUPLICATE KEY... I've posted an example recently on this forum..

Posted: Sun May 07, 2006 2:49 am
by dibyendrah
As specifies by scottayy, simple update process works to increment the table field value :

Code: Select all

mysql> create table test3( x int(11), y int(11), primary key(`x`) );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test3 values(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3 values(2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3 values(3, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+---+------+
| x | y    |
+---+------+
| 1 |    1 |
| 2 |    1 |
| 3 |    2 |
+---+------+
3 rows in set (0.00 sec)

mysql> select * from test3;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
| 2 |    1 |
| 3 |    2 |
+---+------+
3 rows in set (0.00 sec)
But for replace process, it replaced the NULL value when the row with x=4 didn't exist :

Code: Select all

mysql> replace into test3 (x,y) values(4, `y` = `y`+1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test3;
+---+------+
| x | y    |
+---+------+
| 1 |    2 |
| 2 |    1 |
| 3 |    2 |
| 4 | NULL |
+---+------+
4 rows in set (0.00 sec)
why `y` = `y`+1 didn't work ? Amazing ...

:(

Dibyendra

Posted: Sun May 07, 2006 5:15 am
by timvw
dibyendrah wrote: mysql> replace into test3 (x,y) values(4, `y` = `y`+1);
why `y` = `y`+1 didn't work ?
y is not defined (thus NULL in SQL).. So you assign NULL + 1 (equals NULL) to y.

Code: Select all

INSERT INTO test3
VALUES (3, 1)
ON DUPLICATE KEY SET y=y+1

Posted: Sun May 07, 2006 5:36 pm
by raghavan20
I think they do not intend to use mysql variable instead they want to increment the current value of y by one on each replace statement.

dude, you should be using y + 1 instead of y = y + 1; you cannot make assignments in values clause.

Posted: Tue May 09, 2006 6:58 am
by dibyendrah
Thank you for the solution but the I couldn't make use the following:

Code: Select all

ON DUPLICATE KEY 
SET `y` =`y`+1
Thank you all,
Dibyendra