MySQL ODBC Connector - LOAD DATA, Ignore in SET?

Author

LOAD DATA, Ignore in SET?

Jessica Svensson

2006-01-28, 8:23 pm

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 partnumb=@partnumb)
[...]

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.

Thanks in advance.

____________________
____________________
____________________
_____
Hitta rätt på nätet med MSN Search http://search.msn.se/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Paul DuBois

2006-01-28, 8:23 pm

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 partnumb=@partnumb)
>[...]
>
>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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jessica Svensson

2006-01-28, 8:23 pm

>From: Paul DuBois <paul@mysql.com>
>To: "Jessica Svensson" <jessica_s76@hotmail.com>,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:
>
>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?

____________________
____________________
____________________
_____
Nyhet! MSN Messenger i Mobiltelefonen! http://mobile.msn.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Paul DuBois

2006-01-28, 8:23 pm

At 0:07 +0000 1/29/06, Jessica Svensson wrote:
>
>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/mysql? unsub...sie.nctu.edu.tw

Sponsored Links