At 0:07 +0000 1/29/06, Jessica Svensson wrote:
From: Paul DuBois <[EMAIL PROTECTED]>
To: "Jessica Svensson" <[EMAIL PROTECTED]>,mysql@lists.mysql.com
Subject: Re: LOAD DATA, Ignore in SET?
Date: Sat, 28 Jan 2006 17:59:23 -0600
At 23:42 +0000 1/28/06, Jessica Svensson wrote:
I'm doing load data a few times a day via cron and using this:
LOAD DATA
LOCAL
INFILE '/file.txt'
INTO TABLE input
[...]
(@partnumb, description, price)
SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED])
[...]
Now if the partnumber does NOT exists in the products table the
product_id gets the value 0 (zero). I would like to have it ignore
if there is not match, so i don't need to run a seperate query to
delete everything with product_id = 0.
If by "ignore it" you mean "skip the input line and do not load it,"
you can't do that.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Actually i dont care how it's done i just don't want it in my
database. Ignore, Skip, instantly delete... whatever :) Is it
impossible?
LOAD DATA attempts to load every line. The only way it won't happen will
be that some error occurs or you're using IGNORE and a duplicate-key error
occurs. If you want to selectively ignore lines based on some other
criterion, LOAD DATA is probably the wrong approach.
At least if you're loading the data directly into the target table.
You might consider another approach: Load the data into a temporary
table, delete from it those records that have no product_id match (use
the multiple-table DELETE syntax that enables you to delete records based
on join conditions), and then load the remaining records into your target
table (INSERT ... SELECT).
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
