how to update mysql table automatically?

Question

Here is my problem:

    USER TABLE

id  uploader_name   email
1   abhijit         xxx@email.com
2   rahul           abc@email.com


    UPLOAD TABLE

ID  USER_ID     IMAGE_URL
1   1           UPLOAD/XXX.JPG
2   1           UPLOAD/ABC.JPG
3   2           UPLOAD/CD.JPG

How to connect USER_ID in table UPLOAD TABLE with id in table USERS TABLE? So, the USER_ID field automatically update.
Now my problem is how to add data in USER_ID in table UPLOAD TABLE. I am able to add data in every field except USER_ID.

[What have you tried?](http://www.whathaveyoutried.com/) See the [FAQ](http://stackoverflow.com/faq), please. — John Conde, Dec 20 '12 at 06:56

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

In this case you can add a trigger to update the user_id field of upload table. Please refer this link which will help you to know about triggers and how to update the field automatically.

edited May 23 '17 at 11:43

Community

1
1

answered Dec 20 '12 at 07:05

Kapil gopinath

1,053
1
8
18

score 0 · Answer 2 · answered Jun 15 '14 at 09:58

If your question is like: You first add contents to your UPLOAD table, without knowing USER_ID. And, then, you are attempting to patch the USER_ID once you know it. But by that time, the data has been already inserted to the database.

Probable answer: After each insert, you can call LAST_INSERT_ID() to get your auto_increment value of latest inserted table. Use this ID to later patch(UPDATE) the UPLOAD table with this user_id. http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Otherwise if the scene is: You want to update the IDs of user_id if USER table is modified. In this case, please use innodb table with foreign key constraints that makes sure that you keep the proper relationships. Further, use ON UPDATE CASCADE to bring your changes in IDs to the other table.

Anyway, please further explain the problem.

how to update mysql table automatically?

2 Answers2