Is this a valid Update Query
Hi All, Is this a valid query and will it achieve the result of appending a carriage return and some text to the current contents of a text type field: Update Atable set Afield = concat(Afield, \n, Some Text) where KeyField = 'keydata' Thanks in advance. Allen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem importing .csv (excel format) into mysql
try: lines terminated by '\r\n'; -Original Message- From: Chip Wiegand [mailto:[EMAIL PROTECTED] Sent: Thursday, July 08, 2004 3:36 PM To: [EMAIL PROTECTED] Subject: problem importing .csv (excel format) into mysql I was sent an excel file from a remote office, and need to put the data into a mysql database to be displayed on our web site. I removed a few lines of fluff from the excel file and saved it as .csv (using .csv (ms-dos)). When I try to import the file it gives me a duplicate entry for key 1 error. I have looked through the file and the duplicate item it is pointing to does not exist in the file. Here is the error: mysql load data infile '/usr/home/autopilots/whs4.csv' - into table refurbs - fields terminated by ',' - optionally enclosed by '' - lines terminated by '\n'; ERROR 1062: Duplicate entry '2147483647' for key 1 (When I try to import through phpMyAdmin-2.5.6 it doesn't work also, keeps telling me to load a file, which is done by pressing Browse button.) Any ideas what could cause it to fail on a non-existant key? Is there a trick to loading Excel .csv files into mysql? Thanks -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com www.simrad.com [EMAIL PROTECTED] When I'm working on a problem, I never think about beauty. I think only how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong. - R. Buckminster Fuller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing Fixed Length Text Files
Thanks for the response, it was timely and right on the money.
Allen
-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 05, 2004 6:22 PM
To: Allen Weeks; MySQL List
Subject: Re: Importing Fixed Length Text Files
Allen Weeks wrote:
Hello All,
Maybe I am not searching the documentation correctly or am I correct in
finding there is not method of directing importing a fixed length text file
into a MySQL table?
I hope I just missed the reference. Could someone point me to it.
Any assistance is greatly appreciated.
Allen
It's possible, but it's a hassle.
It's documented in:
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
About half way down the page, it says:
If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty
(''), a fixed-row (non-delimited) format is used. With fixed-row format, no
delimiters are used between fields (but you can still have a line
terminator). Instead, column values are written and read using the
``display'' widths of the columns. For example, if a column is declared as
INT(7), values for the column are written using seven-character fields. On
input, values for the column are obtained by reading seven characters. LINES
TERMINATED BY is still used to separate lines. If a line doesn't contain all
fields, the rest of the columns are set to their default values. If you
don't have a line terminator, you should set this to ''. In this case, the
text file must contain all fields for each row. Fixed-row format also
affects handling of NULL values, as described later. Note that fixed-size
format will not work if you are using a multi-byte character set.
So you have to set up a table with the exact field lengths in the 'create
table' definition, and then use 'load data infile' with the options 'fields
terminated by' and 'fields enclosed by' both empty strings ( '' ).
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
Importing Fixed Length Text Files
Hello All, Maybe I am not searching the documentation correctly or am I correct in finding there is not method of directing importing a fixed length text file into a MySQL table? I hope I just missed the reference. Could someone point me to it. Any assistance is greatly appreciated. Allen
Test Message
Hi All, Please pardon the test as I have received no messages from the list in a few days Allen
MySQL 4.1 Production Release
Hi All, Just a quick question, does anyone have a good estimate of when ver 4.1 will go production. Thanks Allen
mysqld-nt vs. mysql-max-nt
Hi All, I can't seem to find any documentation or opinions that would help me decide if it is better to run mysqld-max-nt over the non-max server. Opinions and pointers to supporting docs would be very welcome (especially pointer to docs). Thanks to All in advance. Allen
RE: Round Question
Hi, I tried: Select ceiling(1.2) It worked and returned 2. HTH Allen -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 12:12 PM To: Fabio Bernardo; Mysql (E-mail) Subject: RE: Round Question what I've done is select ceil(value) (not sure if ceil is the function on MySQL, but there is a ceiling function, I'm sure...) -Original Message- From: Fabio Bernardo [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 2:59 PM To: Mysql (E-mail) Subject: Round Question I write this command: Select round(1.1) and obtain 1 as answer Is there a round command to obtain 2 as answer. I mean, in Excel this command is knwon as RoundUp! thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: simple query
Try SELECT * FROM `phrases` WHERE ph like %who%; The percent symbol is the wildcard character for mysql queries HTH -Original Message- From: Pag [mailto:[EMAIL PROTECTED] Sent: Monday, August 25, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: simple query Imagine i have a table phrases with a field ph with contents like these: - who was it - who wasnt it - no i didnt - yes i was - dont know who I want to make a SELECT that gives me only the entries that have the word who: Something like SELECT * FROM `phrases` WHERE ph=who*; I tried the manual but cant make sense of it. How can we use wildcards on selects? Thanks Pag -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow results with simple, well-indexed query
The only thing I can add is check you hardware and OS platform. Cheers -Original Message- From: Jesse Sheidlower [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 11:44 AM To: Cybot Cc: [EMAIL PROTECTED] Subject: Re: Slow results with simple, well-indexed query On Thu, Aug 21, 2003 at 06:01:31PM +0200, Cybot wrote: Jesse Sheidlower wrote: I'm struggling with speed issues on some queries that I would have expected to be relatively fast. Perhaps even more frustratingly, when I've tried to break these down into their components, they still execute very slowly. I've looked over all the relevant suggestions for optimization and so forth, and there's nothing I can tell that I'm missing. An example of a query is to get all the words (the cg.cw field) in a particular alphabetical range that have been added in some timespan (the sref.cd field). The cg table has about 3M rows, and the sref table about 70,000; the intervening tables are all indexed on the relevant id fields: - mysql SELECT cg.cw FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw BETWEEN 't' AND 'tzzz' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) move your DATE before cw AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND cg.cw BETWEEN 't' AND 'tzzz' cause sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) should be faster than cg.cw BETWEEN 't' AND 'tzzz' and so the total rows are already limited when cg.cw BETWEEN 't' AND 'tzzz' will be executed I assume that the optimizer would take care of this, but in any case I gave it a try and it made no difference. also you can try an index with a length of 2 or 3 over cg.cw, this will result in smaller index and possible speed up things I also tried this (the current index is 25 characters on a 100-character field), and if anything it made things slower. Anyone have any other ideas or analysis? Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Simple error ... Im sure
If you are using a MySQL version allowing subselects, try this: SELECT * FROM temp_hits WHERE url NOT IN(SELECT * FROM hits) use parenthesis not brackets hope it helps Allen -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Soren O'Neill Sent: Tuesday, August 19, 2003 2:32 PM To: [EMAIL PROTECTED] Subject: Simple error ... Im sure I keep getting a syntax error, when trying to run this query (perl script): SELECT * FROM temp_hits WHERE url NOT IN SELECT * FROM hits Ive tried with brackets around the last SELECT statement ... no better ... (Essentially, the tables hits, new_hits and temp_hits hold the same columns, what I want to do is extract any rows from new_hits, where the url field is not found in the hits table...) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
