0

for example I have 2 specific columns: id, and threadId, given certain situations, I want the threadId to equal the id (if its the original thread), I am unsure how I would go about this since id is autoIncremented.

j.gooch
  • 305
  • 1
  • 4
  • 21
  • 1
    I don't know if mysql has triggers but if it does you could investigate that option. Otherwise I imagine you'll need to read the record you just inserted, get the id and update the 2nd column. – Greg May 08 '13 at 06:42
  • 4
    You may do better with a design where the `threadid` being *`NULL`* represents that its the original thread. YMMV. – Damien_The_Unbeliever May 08 '13 at 06:45
  • I had no idea that mysql worked that way. literally thought it could basically upload information into itself... fascinating.. testing to make sure my hosting allows triggers – j.gooch May 08 '13 at 06:48
  • damien, that is brilliant sir. – j.gooch May 08 '13 at 07:04
  • 1
    Triggers won't work, because the auto-inc hasn't been assigned in a BEFORE trigger, and you can't change other columns in an AFTER trigger. See my answer to http://stackoverflow.com/questions/1211792/mysql-trigger-to-update-a-field-to-the-value-of-id/1485530#1485530 – Bill Karwin May 08 '13 at 07:06

3 Answers3

1

You should be able to do that with triggers.

Use the information in this post to help you out:
Can you access the auto increment value in MySQL within one statement?

Before insert get the next ID form auto_increment and set it in the new field.

However this poses a problem - you say it should only happen "in certain situations". This means the trigger is no good for you because it will execute every time (unless you have some extra logic in the table to allow checking whether the new field should be set with an IF statement in the trigger). In which case your only option is to insert the row, get its newly created ID and if necessary - update it setting the other column to this value.

Community
  • 1
  • 1
Dimitar K
  • 794
  • 5
  • 9
  • all answers were actually helpful, though my solution was to just do a quick sql query to get the last id as it was. I learned a bit with triggers which is why i chose this answer – j.gooch May 12 '13 at 05:18
1

You have two Options as Mentioned By Greg The first One is to create a trigger on the table that after insert on that Particular table to update the field ID as the same as threadID:

update schema.tablename set threadID = ID;

The other Option would be to execute that sql itself and Update the table.

Stanley Mungai
  • 4,044
  • 30
  • 100
  • 168
1

go with this query INSERT INTO table(id,threadId) VALUES (NULL,(SELECT LAST_INSERT_ID()+1));

Shamseer PC

Shamseer PC
  • 787
  • 2
  • 9
  • 20
  • This is incorrect in general and only applies if you only a single client is inserting data to the table. I would execute an UPDATE immediately after the INSERT and wrap the statements in a transaction. – georgepsarakis May 08 '13 at 17:12