SQL INSERT statement for TWO TABLES at time with INNER JOIN
inner-join, insert, mysql, sql
Solution
If you need to perform the two `INSERT` operations atomically, use a transaction:
START TRANSACTION;
INSERT INTO login_table (username, password) VALUES ('john123', 'passw123');
INSERT INTO user_info (name, address) VALUES ('John', 'wall street');
COMMIT;
N.B. Your storage engine must support transactions for this to work (e.g. `InnoDB`).
To insert multiple values into a table at once, use the multiple rows form of `INSERT`. As stated in the manual:
`INSERT` statements that use `VALUES` syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The values list for each row must be enclosed within parentheses. The following statement is illegal because the number of values in the list does not match the number of column names:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
`VALUE` is a synonym for `VALUES` in this context. Neither implies anything about the number of values lists, and either may be used whether there is a single values list or multiple lists.
Problem
I have two tables `hello` and `login_table` and below is their structure ``` user_info ------- some_id | name | address login_table ------- id | username | password ``` `some_id` and `id` are autoincrement indexes. Now how can i use `INSERT` statement with `INNER JOIN` in `SQL` at present, i want add below data with same `some_id` and `id` ``` `name` = John `address` = wall street `username` = john123 `password` = passw123 ``` below code shows, what i have tried so far. ``` insert into login_table lt INNER JOIN user_info ui ON ui.some_id = lt.id (ui.name, ui.address, lt.username, lt.password) values ('John', 'wall street', 'john123', 'passw123') ``` And this is not the one value, i want to add more than one value at a time.. how can i achieve. thanks for help.