Re: LOAD DATA, Ignore in SET?
At 0:07 + 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 + 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]
Re: LOAD DATA, Ignore in SET?
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 + 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? _ 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/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
At 23:42 + 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA, Ignore in SET?
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. 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/[EMAIL PROTECTED]
