I wanted to update the post table (main table) with those data:
x
User: "helper"
Title: "Title"
Description: "Description"
However, the post table userid column only accepts an integer (foreign keys from the user table (userid)), not username string itself.
How can I do this with INSERT, SELECT/WHERE query?
There are the tables.
post
+--------+--------+---------+-------------+
| postid | userid | title | description |
+--------+--------+---------+-------------+
| 1 | 1 | example | example |
+--------+--------+---------+-------------+
| 2 | 2 | example | example |
+--------+--------+---------+-------------+
| 3 | 3 | example | example |
+--------+--------+---------+-------------+
user
+--------+----------+--------+
| userid | username | roleid |
+--------+----------+--------+
| 1 | admin | 1 |
+--------+----------+--------+
| 2 | helper | 1 |
+--------+----------+--------+
| 3 | test | 2 |
+--------+----------+--------+
Here is what it should look like after the insertion:
post
+--------+--------+---------+-------------+
| postid | userid | title | description |
+--------+--------+---------+-------------+
| 1 | 1 | example | example |
+--------+--------+---------+-------------+
| 2 | 2 | example | example |
+--------+--------+---------+-------------+
| 3 | 3 | example | example |
+--------+--------+---------+-------------+
| 4 | 2 | Title | Description |
+--------+--------+---------+-------------+
Advertisement
Answer
Assuming postid auto-increments:
insert into post (userid, title, description)
select userid, "Title", "Description"
from user
where username="helper"
Note that this will not insert anything if the given username is not found.