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.
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 useVALUES
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 forVALUES
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.