CODEWITHSUNDEEP
mysqldatabaseinsert
Jayseer
Jayseer

Reputation: 199

How to insert data in different tables using MySql database?

Normally I insert data in MySql in one table (sales) example.

string query = "INSERT INTO sales (price, user, date) " +
                             "VALUES(" +
                             "'" + txtQuant.Text + "'," +
                             "'" + txtLog.Text + "'," +
                              "NOW())";

But now I change my mind and decided to separately create another table which will I name settings for column price. this is my first time to do this so I have no idea what to do.

I tried this.

string query = "INSERT INTO sales (user, date), settings (price) " +
                             "VALUES(" +
                             "'" + txtLog.Text + "'," +
                              "NOW())", "'" + txtQuant.Text + "'";

but it doesn't work.

Upvotes: 0

Views: 6534

Answers (4)

Ja͢ck
Ja͢ck

Reputation: 173542

You have to separate the two queries:

INSERT INTO sales (user, date) VALUES ( ... )

Followed by 

INSERT INTO settings (price) VALUES ( ... )

If sales has an auto_increment primary key, you can use LAST_INSERT_ID() in the second query to reference sales.id after the insertion.

INSERT INTO settings (sale_id, price) VALUES (LAST_INSERT_ID(), ... )

Note that correlated queries like these are best put inside a transaction.

Upvotes: 0

Vindicare
Vindicare

Reputation: 213

MySQL doesn't support inserting into multiple tables in a single query (see sql - insert into multiple tables in one query).

You can place multiple queries in a single transaction to ensure that they are either both successful or neither. The above question has an example of this.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

It's not possible with one query as INSERT can only insert data to one table in mysql. You can either

  • write this as two queries and execute them as a batch
  • create a stored procedure that would execute two insert command

You can wrap those inserts in transaction if you need to make sure that both queries will write the data

use those queries

$query1 ="INSERT INTO sales (user, date) values(.....)"; 
$query2 ="INSERT INTO settings (price) values(......)";

Upvotes: 0

Bobby Stenly
Bobby Stenly

Reputation: 1290

just do it with 2 queries

INSERT INTO sales (user, date) values('user', now()); 
INSERT INTO settings (price) values(100);

Upvotes: 1

Related Questions