0

I have a table with main-ids and user-ids. Each user-id has a set of their own unique main-ids, but multiple user-ids can have the same main-id. Is there anyway to increment a main-id for a specific user without having to do 2 queries?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Dan
  • 224
  • 2
  • 11
  • I don't know what you mean by 'increment a `main-id` for a specific user '. Isn't the `main-id` a foreign key into the `main-ids` table? Can you provide the output of `SHOW CREATE TABLE main_ids` and `SHOW CREATE TABLE user_ids`? – Ross Smith II Oct 06 '12 at 16:49
  • The user-id is actually the foreign key. – Dan Oct 06 '12 at 17:18

1 Answers1

0

If you mean like this:

User ID       Main ID
1             1
1             2
1             3
2             1
2             2
2             3

Then you're going to need to make an INSERT trigger that finds the next MainID for that user and stores that.

Alain Collins
  • 16,268
  • 2
  • 32
  • 55
  • that's exactly the structure. Would you be able to guide me on what the trigger would look like? – Dan Oct 06 '12 at 17:17
  • OK, to find the next mainid, you would `select max(mainid) from my_table where user_id = ?` and increment it. Then use that to set the mainid on the current row. Check out [this article](http://stackoverflow.com/questions/1211792/mysql-trigger-to-update-a-field-to-the-value-of-id) which talks about having to do it as an AFTER trigger. Also, watch out for locking (concurrency) issues, and also what happens on UPDATE/DELETE. Good luck. – Alain Collins Oct 06 '12 at 21:53