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]
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 and skip columns in text file...
That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this feature. Thats why i'm woundering if it really havent been impemented in these 5 years that have passed. From: Harald Fuchs [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: 25 May 2005 13:24:55 +0200 In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA and skip columns in text file...
Wow!! Thanks! I will upgrade right away... but i can't find any information in the url you supplied about how to specify what fields from the external file to read... will be quite hard to figure out on my own i guess? Thanks again! From: Harrison Fisk [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: Wed, 25 May 2005 19:03:20 -0400 Hi, On May 25, 2005, at 1:28 PM, Jessica Svensson wrote: That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this feature. Thats why i'm woundering if it really havent been impemented in these 5 years that have passed. The ability to do this was just implemented recently in MySQL 5.0, along with some other enhancements to LOAD DATA INFILE in 5.0. I don't think the documentation has been updated yet to include the new information though. Hopefully it will be soon. You see the changeset and a preliminary description at http://lists.mysql.com/internals/23068 That should be able to do what you want (and more cool stuff!) Prior to that version, you would have to do as he recommended below with a separate staging table. From: Harald Fuchs [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: 25 May 2005 13:24:55 +0200 In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Mobile: +1 315 380-6048 Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html _ Chat: Ha en fest på Habbo Hotel http://habbohotel.msn.se/habbo/sv/channelizer Checka in här! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA and skip columns in text file...
I cant tell you how much i love you right now :) This works flawless!! Thanks a million times! From: Harrison Fisk [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: Wed, 25 May 2005 20:05:40 -0400 Hi, On May 25, 2005, at 7:38 PM, Jessica Svensson wrote: Wow!! Thanks! I will upgrade right away... but i can't find any information in the url you supplied about how to specify what fields from the external file to read... It will read all of them, but you can just specify a variable for ones you don't care about and have it ignore them. So if my text file has 5 columns, but I only want 3 you can say: LOAD DATA INFILE 'data.txt' INTO TABLE tmp (a, @useless, @ignoreme, b, c) Then you get the first field as a, and the 4th and 5th as b and c respectively. The @useless and the @ignoreme variables are just thrown out, since you don't use them. I haven't actually tested this, but it should work that way from what I have seen. The other neat new thing is that you can now pre-process the data before it is loaded. For example, you can use STR_TO_DATE to fix a datetime field before it is loaded into the table directly. will be quite hard to figure out on my own i guess? Thanks again! From: Harrison Fisk [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: Wed, 25 May 2005 19:03:20 -0400 Hi, On May 25, 2005, at 1:28 PM, Jessica Svensson wrote: That just complicates things alot since i get around 200 files, 6 times a day via an automated process and every textfile looks different from the other. To just have different load data would make it much easier. I have read alot of questions about just this and many people is asking for this feature. Thats why i'm woundering if it really havent been impemented in these 5 years that have passed. The ability to do this was just implemented recently in MySQL 5.0, along with some other enhancements to LOAD DATA INFILE in 5.0. I don't think the documentation has been updated yet to include the new information though. Hopefully it will be soon. You see the changeset and a preliminary description at http://lists.mysql.com/internals/23068 That should be able to do what you want (and more cool stuff!) Prior to that version, you would have to do as he recommended below with a separate staging table. From: Harald Fuchs [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: LOAD DATA and skip columns in text file... Date: 25 May 2005 13:24:55 +0200 In article [EMAIL PROTECTED], Jessica Svensson [EMAIL PROTECTED] writes: LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Maybe they didn't implement it yet because there's an easy workaround: create a temporary table including the columns to be skipped, LOAD it, and then use INSERT..SELECT to copy over only the columns you're interested in. Habbo Hotel http://habbohotel.msn.se/habbo/sv/channelizer Checka in här! Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html _ 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]
LOAD DATA and skip columns in text file...
LOAD DATA and skip columns in text file... What i have found out is that this is not possible in any existing version of mysql, correct? I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while searching on google. It said We have entered this on our TODO list one month ago. So that would be almost exactly 5 years ago... is it really that this function has not been implemented during these 5 years? If so, then i guess it could be 5 more years before its impelemented and that i should maybe look for other solutions. Thanks! _ Nyhet! Hotmail direkt i din Mobil! http://mobile.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help me: Boolean fulltext searches, AND instead of OR
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN
MODE);
sure there must be an easy way to change the default word separator to AND
instead of OR?
I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was
possible.
black cat is only an example and the real query comes from user input. So
it can be anything like +cat -dog +big nose -horse white black -red
so parsing the input is not what i want, i just want to change the default
word separator to AND instead of OR.
Now i´m running 4.1.10 and also tried with 4.0.24
Please help me, there must be a way to change this?!?!
_
Hitta rätt på nätet med MSN Sök http://search.msn.se/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help me: Boolean fulltext searches, AND instead of OR
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN
MODE);
sure there must be an easy way to change the default word separator to AND
instead of OR?
I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was
possible.
I know that i could put a + infront of every word but thats not what i want
since the query comes from a searchbox and i dont want to write som code to
manipulate the indata so ite shows correctly i just want to change the
default word separator from OR to AND.
Maybe it´s ft_boolean_syntax that should be changed? If so to what... please
help me before i try to strangle myself :P
_
Chatt: Träffa nya nätkompisar på Habbo Hotel
http://habbohotel.msn.se/habbo/sv/channelizer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
Hi that works fine only now if a user puts + in front of a word that word
becomes optional = OR?
What i would like is the search to work exactly like before only that it
defaults to AND instead of OR.
Almost every search engine i have tried, google etc. works like this.
black horse cat dog only show results with all words present.
This is what i did, set global ft_boolean_syntax = ' +-()~*:|'
I really appreciate your help!!
From: Sergei Golubchik [EMAIL PROTECTED]
To: Jessica Svensson [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR
Date: Wed, 23 Mar 2005 22:32:49 +0100
Hi!
On Mar 23, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that
contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
BOOLEAN MODE);
sure there must be an easy way to change the default word separator to
AND
instead of OR?
There is. See ft_boolean_syntax variable - it defines what characters is
used for each operator. In particular it defines '+' for must be
present and a space ' ' for optionally present words.
You want to put the space first (for must be present words)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
_
Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
