Adding new data to a table in MYSQL-CodePudding

I'm self learning MySQL so I'm confused when writing the query statement. Hope someone will help me. I have 2 tables

Table 1: User

id   name    age   address_id

1    John    25    1



Table 2: Address

id      city
 
1       Tokyo
2       Paris
3       Seoul

I want to add a user named "William", age 30, city Paris. I will write the command like this:

INSERT INTO User (id, name, age, address_id) values (2, "William", 30, 2);

However I want to use a query statement to get the "id" value of the city "Paris" and then add the obtained "id" value to "address_id" and use the "Insert" statement to add the value in without having to manually add " 2 " as I wrote above. Can I achieve the goal with this method? Is there any sample code for me? I would be very grateful and appreciative of that. Thanks

CodePudding user response：

Here's one way to do it:

INSERT INTO User (id, name, age, address_id)
SELECT 2, 'William', 30, id
FROM Address
WHERE city = 'Paris'

Documentation: https://dev.mysql.com/doc/refman/8.0/en/insert-select.html