CODEWITHSUNDEEP
phpmysqlwordpresscsv
eugene a.
eugene a.

Reputation: 93

MySQL LOAD DATA into two related tables

In MySQL query, after we do LOAD DATA statement is it possible to get the last inserted ID to be used in the LOAD DATA for other table? Generally speaking, is it possible to use LOAD DATA for 2 related tables?

Upvotes: 1

Views: 297

Answers (2)

Kimutai
Kimutai

Reputation: 1052

For example if we run the following insert statement:

mysql_query("INSERT INTO mytable (name,age) VALUES ('Steve',25)") or die(mysql_error());

We can always get the last inserted id by using this function

mysql_insert_id();

So we can move the id to another table this way

$last_id = mysql_insert_id();
mysql_query("INSERT INTO mytable2 (uid) VALUES('".$last_id."')") or die(mysql_error());

Upvotes: 0

spencer7593
spencer7593

Reputation: 108460

No. It's not possible using the LOAD DATA INFILE syntax. (The LAST_INSERT_ID() function returns only a single value, not the values for a set of rows, so unless you are inserting individual rows, and in that case, you wouldn't expect that LOAD DATA INFILE would be used to insert individual rows.)

The workaround (if I'm understanding what you want to accomplish), is to make use of an intermediate table as a target for the load. Truncate the table (or drop and re-create it), and load rows using LOAD DATA INFILE.

Then, use SQL statements to pull from that table, and insert/update other tables. Including an AUTO_INCREMENT id column in the intermediate table is a convenient way to relate rows, if you don't have some other unique key.

Upvotes: 1

Related Questions