Query for insert one record (ID) from one table to another table

Question

Please help me with mysql query...

i have this situation:

table 1 -- tasks

**id**|task_name|status|created_at|updated_at|user_id

and table 2 -- samples

id|sample_name|...|...|...many other things|**task_id**|user_id

i want that in table 2 in column "task_id" is "id" from table 1 or "tasks.id" ?

I have this query but it's result is number 1 in each column instead of the id of each entry..

INSERT INTO samples(task_id), 
SELECT tasks.id from tasks
JOIN samples
ON task_id = tasks.id

thanks for help...

Possible duplicate of [INSERT with SELECT](https://stackoverflow.com/questions/5391344/insert-with-select) — coder001, Feb 22 '18 at 09:23
If you see the anwser in the comment, I think you just can't do a INSERT INTO on ONE column if your table has lot of column and no default value. You have to make somehting like `INSERT INTO (column1, column2,...columnN) VALUES ('value1', 'value2',...'valueN')` and if you need you can make a SELECT instead of the VALUE part `SELECT column1, column2,...columnN FROM...`. Don't know if it's clear but it's what I understand after reading the INSERT with SELECT anwser — Mickaël Leger, Feb 22 '18 at 09:28

Michael · Answer 1 · 2018-07-14T10:12:04.477

Assuming you are doing this through PHP (based on you tagging it) and assuming that you need an insert into both tables, the basic jist would be (also assuming the id field in tasks is auto-increment):

    $stmt1 = $conn->prepare("INSERT INTO tasks (fields, other_fields) VALUES  (?, ?)"))
    {
    $stmt1->bind_param("ss",$fields, $other_fields);
    $stmt1->execute();  
    $lastid = $conn->insert_id;
    $stmt1->close();
    }

Now you can use the variable $lastid as the value when you insert the samples data.

        $stmt2 = $conn->prepare("INSERT INTO samples (id, other_fields, task_id) VALUES  (?, ?, /)"))
        {
        $stmt2->bind_param("isi",$ID, $other_fields, $lastid);
        $stmt2->execute();
        $stmt2->close();
        }

If samples already exists and you need to update it with the id from tasks, you'd just update samples after the insert into tasks, assuming you have something to use in the where clause that can uniquely identify the record you want updated :

 $stmt2 = $conn->prepare("UPDATE samples set task_id = ? where user_id - ?)
 $stmt2->bind_param("ii",$lastid, $user_id);
 $stmt2->execute();
 $stmt2->close();
 }
 else {
 die(mysqli_error($conn));
 }

I'm making a lot of assumptions, I know. But I can't comment yet so this is my only way of assisting.

I'm not downvoting you because you cannot use comments, but next time if you don't have the answer don't post an assumption, without understanding the actual requirements. — Cemal, Feb 22 '18 at 09:49

score -2 · Answer 2 · answered Feb 22 '18 at 09:31

-2

Unfortunately your method is not true and not logical. At you at the moment of recording there is no field which could unite the data of the table, so you need to get the task_id first and then insert it into the query

answered Feb 22 '18 at 09:31

Mykhailo YATSYSHYN

181
9

1

I downvoted because you need to use comments for this. – Cemal Feb 22 '18 at 09:48

Query for insert one record (ID) from one table to another table

2 Answers2