Insert multi table with multi query and roller back if false

Databases
1

I have 3 tables like this:

post(post_id, post_author, post_date, post_status, post_updated, post_image);
post_lang(post_id, id_lang, post_title, post_intro, post_content);
post_shop(post_id, id_shop);

Example, I have 3 queries: insert_table_query_1 (1), insert_table_query_2 (2), insert_table_query_3 (3). Suppose (1) run ok, both (2) and (3) run fail. My problem is, if any insert query runs false then roller back data is inserted before. Please help me!

P/S: the first time I want to use 1 query to insert multi tables but I searched on gg they said I can’t. I only found the query can use for update. This is:

UPDATE ps_post p
INNER JOIN ps_post_lang pl ON p.`post_id` = pl.`post_id`
INNER JOIN ps_post_shop ps ON pl.`post_id` = ps.`post_id`
SET `post_intro` = 'Intro 1'
WHERE ps.`id_shop` = 1 AND pl.`id_lang` = 1 AND p.`post_id` = 1;
2

If you need to perform multiple statements as one undividable unit you should use transactions.

You can wrap the insert statement within a transaction and if any of the statements fail you undo any operations by doing a rollback otherwise if they are all successful you commit the transaction. More information on this can be found in the manuals for your DBMS and the client side language.

3

Thanks! I see this query: INSERT INTO t5 (columns) SELECT <columns> FROM t1 JOIN t2 ON t1.pk=t2.fk JOIN t3 ON… from here #1 and [URL=“http://stackoverflow.com/questions/17628035/mysql-insert-multiple-table-value-in-one?rq=1”]here #1. I don’t understand how they can insert into tables without VALUES?

4

There is nothing strange about that. In an insert statement there are two ways to specify what data should be inserted, either by using a values clause or a select query.

You are digressing though.

5

I got it :slight_smile:

6

Are the field values defined as NOT NULL ??

7

To expand on SwampBookie’s reply, it’s important to understand that SELECT operations on relational databases both consume and return relations (tables). It’s this simplicity that underpins the relational model.

Thus, if you have the following relation:
UserDetails


+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(3)       | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)  | NO   |     | NULL    |                |
| email    | varchar(100) | NO   |     | NULL    |                |
| age      | int(2)       | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

And you’d like to insert all users who are older than 17 years old (for some reason?) into the following relation:
OfAgeUsers


+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(3)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | NO   |     | NULL    |                |
| age      | int(2)      | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

Then you’d perform an INSERT…SELECT statement, where the returned data from the SELECT statement correlates to both the attribute (column) count and the respective attribute domains of the relation to insert into:


INSERT INTO OfAgeUsers (username, age) SELECT username, age FROM UserDetails WHERE age > 17;

Whilst the above demonstrative example was not very useful, INSERT…SELECT statements can be very handy in particular scenarios, such as creating materialised views.