Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
o11w
Forum Newbie
Posts: 3 Joined: Fri Mar 24, 2006 2:33 am
Post
by o11w » Fri May 05, 2006 10:02 am
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Fri May 05, 2006 10:34 am
++ 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 » Fri May 05, 2006 11:04 am
yes i'v tried this also.. but i doesn't work... :/
s.dot
Tranquility In Moderation
Posts: 5001 Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana
Post
by s.dot » Fri May 05, 2006 11:08 am
Would a simple update work?
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 » Fri May 05, 2006 11:12 am
I thought to use the REPLACE because if the records don't exist (where a new season occures), it will create the record.
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Fri May 05, 2006 2:37 pm
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 » Fri May 05, 2006 3:08 pm
Read the manual for INSERT and look at the ON DUPLICATE KEY... I've posted an example recently on this forum..
dibyendrah
Forum Contributor
Posts: 491 Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:
Post
by dibyendrah » Sun May 07, 2006 2:49 am
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 » Sun May 07, 2006 5:15 am
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
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Sun May 07, 2006 5:36 pm
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.
dibyendrah
Forum Contributor
Posts: 491 Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:
Post
by dibyendrah » Tue May 09, 2006 6:58 am
Thank you for the solution but the I couldn't make use the following:
Thank you all,
Dibyendra