Insert query with an inner join

Question

I want to make my insert query which has an inner join to a user's data table.

The example of the tables is like this:

users:

uid | name 

---

users_data:

id | data_id | uid | other fields

---

data_stocks

id | data_id | quantity

---

So im trying to insert into data_stocks relating to only knowing the uid from users.

Kinda of like this:

INSERT INTO data_stocks (data_id,quantity)
VALUES (' need to join it some how ','$quantity');

Is this possible in mySQL?

Answer 1

You want to use the insert ... select form of the statement:

INSERT INTO data_stocks (data_id,quantity)
    select ud.data_id, $quantity
    from users u join
         users_data ud
         on u.uid = ud.uid;

If you are doing this for only one user, it might look more like this:

INSERT INTO data_stocks (data_id,quantity)
    select ud.data_id, $quantity
    from users_data ud
    where ud.uid = $uid;