CODEWITHSUNDEEP
mysqlsql
j.gooch
j.gooch

Reputation: 305

in mysql, how do you put the data from an auto incremented field into another column?

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.

Upvotes: 0

Views: 77

Answers (3)

Shamseer PC
Shamseer PC

Reputation: 817

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

Shamseer PC

Upvotes: 1

Stanley Mungai
Stanley Mungai

Reputation: 4150

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.

Upvotes: 1

Dimitar K
Dimitar K

Reputation: 912

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.

Upvotes: 1

Related Questions