CODEWITHSUNDEEP
sqlinsert
user941537
user941537

Reputation: 13

sql, adding to table with a value coming from another table

So I have table A and table B. I insert a entry into table A and this will generate a id in table A. Now I want to use this id to insert another entry in table B. The entry I am creating in table A has a unique field I can search to get the entry I created. Thanks for all who try to help in advance!

Upvotes: 1

Views: 81

Answers (3)

aaaaaa
aaaaaa

Reputation: 2618

If using PHP:

I guess when you say "this will generate an id", you mean the column is AUTO_INCREMENT so the id will be set by MySQL.

Then you should use mysql_insert_id() or PDO::lastInsertId with PDO.

That should then look like :

mysql_query("INSERT INTO table_A ...");
$last_id_in_table_A = mysql_insert_id();
mysql_query("INSERT INTO table_B (id_table_A) VALUES ($last_id_in_table_A)");

Upvotes: 0

Stefan Mai
Stefan Mai

Reputation: 23939

Maybe a little cleaner?

INSERT INTO table_b
SELECT
  id,
  value2
FROM table_a
WHERE unique_column = 'value'

Upvotes: 1

Joe
Joe

Reputation: 15802

Something like this should work.

INSERT INTO table_b
(column1, column2, etc)
VALUES
(
    ( SELECT id FROM table_a WHERE unique_column = 'value' LIMIT 1 ),
    value2,
    etc
)

As an alternative, if you're using PHP you can use mysql_insert_id or a similar function to get the ID that a row was inserted into table A with.

Upvotes: 0

Related Questions