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?
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:
Thank you all,
Dibyendra