0

I have a table notes which is described as :

+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL              | auto_increment |
| date    | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
| content | varchar(400) | YES  |     |                   |                |
| page    | int(11)      | YES  |     | -1                |                |
| user_id | int(11)      | YES  | MUL | NULL              |                |
| hasmore | bit(1)       | YES  |     | b'0'              |                |
+---------+--------------+------+-----+-------------------+----------------+

Where page represent page number.
I am unable to create a trigger that can automatically set the page number for my notes when a page is inserted.
And the reason was this statement is not working. 

update notes set page = (select count(*) from notes);

It seems that I cannot query notes to update value of notes itself.
Is there any other method to accomplish my task easily.

The error I am getting is : 

ERROR 1093 (HY000): You can't specify target table 'notes' for update in FROM clause

Edit :
Suppose if I have 2 entries in my table

>select id, user_id, page, content from notes where user_id = 2;
1  |  2  |  0  | "hi"
2  |  2  |  1  | "welcome"

And I insert new data

>insert into notes (user_id, content) values (2, "hello");

Then my table should be like :

>select id, user_id, page from notes where user_id = 2;
1  |  2  |  0  |  "hi"
2  |  2  |  1  |  "welcome"
3  |  2  |  2  |  "hello"
afzalex
  • 8,598
  • 2
  • 34
  • 61
  • There are ways to do that but before that please clarify on what exactly the update statement should do, if possible provide some sample data and expected output. – Abhik Chakraborty Jun 02 '15 at 09:07
  • I'm not convinced that you should be storing this. – Strawberry Jun 02 '15 at 09:09
  • @Strawberry I am not expert in mysql, but I would like to know the reason for not storing page numbers. Actually I didn't wanted this work to be done from server side scripts. But if it is good somehow to do it from server side scripts then I will. – afzalex Jun 02 '15 at 09:16
  • Generally, you should not store data that can be easily derived from other data. What if someone updates or deletes an id? Then all your page numbers will be wrong, and you won't even know about it. Also, your 'Edit' is not properly representative of the problem. – Strawberry Jun 02 '15 at 09:19
  • @Strawberry Can you suggest me other approach to do the same? And I will change my table because it is not correct but still I want to know how to create page numbers in similar kind of situations (perhaps with triggers). and cannot I maintain it by creating trigger for delete too. – afzalex Jun 02 '15 at 09:20
  • Yes. Just calculate the page numbers 'on-the-fly' - as and when you need them. – Strawberry Jun 02 '15 at 09:23
  • Have you thought about using auto increment. In fact, is not your ID field in fact what you want page to be? – Jim Mc Jun 02 '15 at 14:53
  • @JimMc each user *identified by user_id* can have multiple pages every day *identified by date*. So I cannot use `auto_increment` in this case. – afzalex Jun 02 '15 at 19:14
  • Missed that, don't know why. Following up on Strawberry's suggestion you should be able to compute needed page numbers on the fly using @ variables and a derived table. If you need sticky page numbers, use one of the workarounds here: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Jim Mc Jun 03 '15 at 18:12

0 Answers0