REPLACE and increment

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
o11w
Forum Newbie
Posts: 3
Joined: Fri Mar 24, 2006 2:33 am

REPLACE and increment

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

++ isn't an operator in MySQL.

Try n+1
o11w
Forum Newbie
Posts: 3
Joined: Fri Mar 24, 2006 2:33 am

Post by o11w »

yes i'v tried this also.. but i doesn't work... :/
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Would a simple update work?

Code: Select all

UPDATE `table` SET `n`=`n`+1
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
o11w
Forum Newbie
Posts: 3
Joined: Fri Mar 24, 2006 2:33 am

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Read the manual for INSERT and look at the ON DUPLICATE KEY... I've posted an example recently on this forum..
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
Post Reply