1

I am trying to insert data into a table, however I would like to select the ID which is auto incremented when the row is made, and then I would like to set it into a field within another table.

For example

  1. INSERT INTO playerdata (Username) VALUES ('%e')
  2. SELECT ID FROM playerdata p WHERE Username = '%e' LIMIT 1
  3. UPDATE masterdata SET Slot = p.ID

I have been trying to find out a way for a hours now, but still no luck, so if there is a way to do this, please reply with it or if there is an easier way, then please explain. All replies are gratefully appreciated.

Improve this question
1
  • Are you using any programming languages or just sql? Commented Apr 11, 2016 at 23:47

3 Answers 3

Reset to default
1

you can use a trigger after insert like this

create trigger update_masterdata after insert on playerdata for each row begin UPDATE masterdata SET Slot = NEW.id; end/

sqlfiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

But I need to use SELECT to gather the ID after the row has been inserted, how would I do this with the trigger?
Oh you need the ID for something else other than Updating masterdata..then i guess you have to run a query searching for that username.
No, I mean-.. I need to insert the data into playerdata, then I need to update the masterdata using the new ID created when inserting the data, I am not assuming that "NEW" on the "NEW.id" already does this? It was my mistake
the NEW.id will be the auto_incremented value that was inserted when an insert was called.
on the sqlfiddle i set delimiter as /... you'll have to specify your own delimiter or use the appropriate one
|
1

Sounds like what you're looking for is a DML trigger upon insert.

https://msdn.microsoft.com/en-us/library/ms189799.aspx

Improve this answer

1 Comment

Thank you, it seems like it will do the trick, I will take a look at it and try it out.
1

Seems you only insert 1 rows.

You can try LAST_INSERT_ID function

Improve this answer

1 Comment

I would be able to use this-.. however I am coding for SA-MP using BlueG's MySQL plugin and it doesn't include this function in it, thank you for the help though.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.