I wanted to update the post table (main table) with those data:
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.