MYSQL multiple inserts/updates question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
myleow
Forum Contributor
Posts: 194
Joined: Mon Jun 21, 2004 7:05 pm
Location: California

MYSQL multiple inserts/updates question

Post by myleow »

I remember you can have multiple inserts/updates but forgot how do you do it.

if you have 2 SQL

$sql1="UPDATE table SET item=1 WHERE id=1";

$sql2="INSERT INTO table (item) VALUES ('1');

How do you make it into a string to run it once so its committed into the DB?

Then there is the other question, because there are 40,000 items to be either updated or inserted depending if they are new or existing. Would it be advisable to send a string of 40,000 items to mysql_query?

Which would be faster? doing each query as they come up or compile them together and doing it all at once?

NOTE: 90% of the queries should be INSERT.

Thanks in advance.

Mian
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

might want to look into REPLACE syntax.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Which would be faster? doing each query as they come up or compile them together and doing it all at once?
ALTER TABLE <tablename> DISABLE KEYS + LOAD DATA INFILE should be much faster.

Btw, reading the manual is known to increase database performance considerably :). For example, there's a page on INSERT statements speed
Post Reply