UPDATE and INSERT in one
Hi, I want to update a table if it meets some conditions (already exist) and INSERT otherwise? Right now I am doing it this way: 1. SELECT the record 2. If it exist, I UPDATE it 3. If it does not exit, I INSERT a new record Could this be done in one query? I mean like conditional update/insert? /Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE and INSERT in one
Hi, what you want to look into is (depending on your version) either INSERT ... ON DUPLICATE KEY UPDATE ... or REPLACE http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html http://dev.mysql.com/doc/refman/5.0/en/replace.html /Johan [EMAIL PROTECTED] skrev: Hi, I want to update a table if it meets some conditions (already exist) and INSERT otherwise? Right now I am doing it this way: 1. SELECT the record 2. If it exist, I UPDATE it 3. If it does not exit, I INSERT a new record Could this be done in one query? I mean like conditional update/insert? /Peter --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 269.5.5/769 - Release Date: 2007-04-19 17:56 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How could write this SQL
Hi, How could select row1 a1,b1.qty, b2.qty, b3.qty row2 a2,b1.qty, b2.qty, b3.qty row3 a3,b1.qty, b2.qty, b3.qty ... from a table : row1 a1,b1,qty row2 a1,b2,qty row3 a1,b3,qty row4 a2,b1,qty row5 a2,b2,qty row6 a2,b3,qty row7 a3,b1,qty Best regard! Shuming Wang
RE: character_set_xxx
How can I change character_set_xxx variables in MySQL 4.1.x in Linux? SET NAMES 'charset' http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE and INSERT in one
[EMAIL PROTECTED] wrote: I want to update a table if it meets some conditions (already exist) and INSERT otherwise? Right now I am doing it this way: 1. SELECT the record 2. If it exist, I UPDATE it 3. If it does not exit, I INSERT a new record Could this be done in one query? I mean like conditional update/insert? Take a look at REPLACE: http://dev.mysql.com/doc/refman/5.0/en/replace.html -- David Precious http://blog.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE and INSERT in one
insert into table1 (fa,fb,qty) values (fa1,fb1,qty1) on duplicate key update qty=qty+qty1 a primarykey or unique key must. Shuming Wang
Re: Storing forests in tables.
Nathan, I ve recently been playing with storing trees in mysql using nested set, and I was wondering if anyone knows if this could be extended to forests (set of trees) to be stored in one table? Dead simple, add a treeID smallint column to the table that holds the nested sets, and add ... WHERE treeID = treeid ... to every update, delete, insert select. PB - Nathan Harmston wrote: Hi, I ve recently been playing with storing trees in mysql using nested set, and I was wondering if anyone knows if this could be extended to forests (set of trees) to be stored in one table? Many Thanks in advance, Nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY multiple columns
Hi, I have a query that returns data from a join of two tables, person and company. The results look like: FIRST_NAME | LAST_NAME | COMPANY_NAME - NULL | NULL | Toy Co Mark | Smith | NULL NULL | NULL | Big Corp NULL | NULL | Acme Ltd Lucy | Jones | NULL I want to be able to order these results alphabetically by name (first_name,last_name or company_name), regardless of whether they are a person or company, to get: FIRST_NAME | LAST_NAME | COMPANY_NAME - NULL | NULL | Acme Ltd NULL | NULL | Big Corp Lucy | Jones | NULL Mark | Smith | NULL NULL | NULL | Toy Co If I ORDER BY first_name, last_name, company_name I get all the companies followed by all the people (due to the NULLs in the first_name and last_name fields). Ideally I should be able to achieve this with ORDER BY CONCAT(first_name,last_name,company_name) but of course this doesn't work because CONCAT returns NULL if any of its arguments are NULL (which will always be the case). Any ideas? Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY multiple columns
Hi Edward, Edward Kay wrote: Hi, I have a query that returns data from a join of two tables, person and company. The results look like: FIRST_NAME | LAST_NAME | COMPANY_NAME - NULL | NULL | Toy Co Mark | Smith | NULL NULL | NULL | Big Corp NULL | NULL | Acme Ltd Lucy | Jones | NULL I want to be able to order these results alphabetically by name (first_name,last_name or company_name), regardless of whether they are a person or company, to get: FIRST_NAME | LAST_NAME | COMPANY_NAME - NULL | NULL | Acme Ltd NULL | NULL | Big Corp Lucy | Jones | NULL Mark | Smith | NULL NULL | NULL | Toy Co If I ORDER BY first_name, last_name, company_name I get all the companies followed by all the people (due to the NULLs in the first_name and last_name fields). Ideally I should be able to achieve this with ORDER BY CONCAT(first_name,last_name,company_name) but of course this doesn't work because CONCAT returns NULL if any of its arguments are NULL (which will always be the case). You could use COALESCE(). It returns the first non-null item in the list. Baron -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for people with similar data for exchange of ideas
Hi all, I am hoping to find someone with similar data, who is using mysql to exchange ideas and concepts. A second opinion is always good I guess. I work in a research setup and store mainly trial related, genotypic and phenotypic information. As technology progresses and we are expecting 1M chip SNP data soon I was wondering if anyone else is already storing that or at least 550K or alike. As I work mainly with autism data it would be also nice to exchange some ideas regarding the storage of test results (ADI,ADOS, etc). I guess this is a long shot but I thought I would give it a shot Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY multiple columns
From: Baron Schwartz Hi Edward, Edward Kay wrote: Hi, I have a query that returns data from a join of two tables, person and company. The results look like: FIRST_NAME | LAST_NAME | COMPANY_NAME - NULL | NULL | Toy Co Mark | Smith | NULL NULL | NULL | Big Corp NULL | NULL | Acme Ltd Lucy | Jones | NULL I want to be able to order these results alphabetically by name (first_name,last_name or company_name), regardless of whether they are a person or company, to get: FIRST_NAME | LAST_NAME | COMPANY_NAME - NULL | NULL | Acme Ltd NULL | NULL | Big Corp Lucy | Jones | NULL Mark | Smith | NULL NULL | NULL | Toy Co If I ORDER BY first_name, last_name, company_name I get all the companies followed by all the people (due to the NULLs in the first_name and last_name fields). Ideally I should be able to achieve this with ORDER BY CONCAT(first_name,last_name,company_name) but of course this doesn't work because CONCAT returns NULL if any of its arguments are NULL (which will always be the case). You could use COALESCE(). It returns the first non-null item in the list. Baron Excellent - that does the trick! Thanks :) Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : How could write this SQL
In case it is acceptable to have a1,b1.qty, b2.qty, b3.qty concatenated in one field GROUP_CONCAT might help http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_ group-concat -- Jacques Brignon -Message d'origine- De : Baron Schwartz [mailto:[EMAIL PROTECTED] Envoyé : vendredi 20 avril 2007 14:52 À : [EMAIL PROTECTED] Cc : mysql@lists.mysql.com Objet : Re: How could write this SQL Hi, wang shuming wrote: Hi, How could select row1 a1,b1.qty, b2.qty, b3.qty row2 a2,b1.qty, b2.qty, b3.qty row3 a3,b1.qty, b2.qty, b3.qty ... from a table : row1 a1,b1,qty row2 a1,b2,qty row3 a1,b3,qty row4 a2,b1,qty row5 a2,b2,qty row6 a2,b3,qty row7 a3,b1,qty I think you are describing a cross-tabulation (aka pivot table). You can do this with joins, though this particular case looks tricky. There is a good article on cross-tabulations here: http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html Baron -- 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: How could write this SQL
Hi, wang shuming wrote: Hi, How could select row1 a1,b1.qty, b2.qty, b3.qty row2 a2,b1.qty, b2.qty, b3.qty row3 a3,b1.qty, b2.qty, b3.qty ... from a table : row1 a1,b1,qty row2 a1,b2,qty row3 a1,b3,qty row4 a2,b1,qty row5 a2,b2,qty row6 a2,b3,qty row7 a3,b1,qty I think you are describing a cross-tabulation (aka pivot table). You can do this with joins, though this particular case looks tricky. There is a good article on cross-tabulations here: http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Could someone explain
Dear List, We are running: mysql status -- mysql Ver 14.12 Distrib 5.0.27, for unknown-freebsd6.0 (i386) using readline 5.0 Could someone explain the meaning or give us brief explanation of the following entries in err file on MySQL server: == Status information: Current dir: /mysql/mysql_data/data/ Running threads: 1 Stack size: 196608 Current locks: lock: 0xac9623c: lock: 0xac8da3c: lock: 0xac60a3c: lock: 0xac5aa3c: lock: 0xac54a3c: lock: 0xac47a3c: lock: 0xac4423c: lock: 0xac3d23c: Key caches: default Buffer_size: 268435456 Block_size: 1024 Division_limit:100 Age_limit: 300 blocks used: 895 not flushed: 0 w_requests: 3 writes: 1 r_requests: 185177 reads: 895 handler status: read_key: 116 read_next: 98382 read_rnd 0 read_first: 3 write: 96 delete 3 update: 0 Table status: Opened tables: 14 Open tables:8 Open files:21 Open streams: 0 Alarm status: Active alarms: 1 Max used alarms: 1 Next alarm time: 28799 = Best Regards, Mikhail Berman
Storing forests in tables.
Hi, I ve recently been playing with storing trees in mysql using nested set, and I was wondering if anyone knows if this could be extended to forests (set of trees) to be stored in one table? Many Thanks in advance, Nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
Hi! Duncan Hill wrote: On Thursday 19 April 2007 15:53:54 molemenacer wrote: I am trying to change all the names of the database from mthosp to another name, is this possible? Assuming you mean tables, not database (as mysqldump doesn't store the database name in the dump file [or at least never has for me]): sed -e 's/mthosp/another_name/' source.sql dest.sql 1) This is risky, because it will also change (for example) govmthospital to govanother_nameital which may be a bit more than is intended. Sure, you can add conditions that prevent some such issues, but it will get complicated. The regular expressions the sed can handle are somewhat limited, when you compare them to what Perl can do. (Sorry, I won't give a Perl command - this is still too risky.) mysql rename table mthosp_1 to another_name_1, mthosp_2 to another_name_2 (Check the manual for syntax) 2) If you want multiple changes in a line, add the g modifier at the command end: sed -e 's/mthosp/another_name/g' source.sql dest.sql 3) Most likely, you should first run a grep on the file, to check where the string occurs and which effects your commands have. 4) *If* you decide to use sed, then a cheap way to see just the changes is this: sed -n -e 's/mthosp/another_name/gp' source.sql verify.sql This will output *only* the changed lines, not the unchanged ones. However, it will not show the context - if you want to get that as well, then you will need a more complicated sed command or (easier, IMHO) a pipe of grep (providing the context, see the -A and -B options) and sed (above). Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
advice for blob tables?
Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
Joerg Bruehe wrote: Hi! Duncan Hill wrote: On Thursday 19 April 2007 15:53:54 molemenacer wrote: I am trying to change all the names of the database from mthosp to another name, is this possible? Assuming you mean tables, not database (as mysqldump doesn't store the database name in the dump file [or at least never has for me]): sed -e 's/mthosp/another_name/' source.sql dest.sql 1) This is risky, because it will also change (for example) govmthospital to govanother_nameital which may be a bit more than is intended. Sure, you can add conditions that prevent some such issues, but it will get complicated. The regular expressions the sed can handle are somewhat limited, when you compare them to what Perl can do. (Sorry, I won't give a Perl command - this is still too risky.) mysql rename table mthosp_1 to another_name_1, mthosp_2 to another_name_2 (Check the manual for syntax) 2) If you want multiple changes in a line, add the g modifier at the command end: sed -e 's/mthosp/another_name/g' source.sql dest.sql 3) Most likely, you should first run a grep on the file, to check where the string occurs and which effects your commands have. 4) *If* you decide to use sed, then a cheap way to see just the changes is this: sed -n -e 's/mthosp/another_name/gp' source.sql verify.sql This will output *only* the changed lines, not the unchanged ones. However, it will not show the context - if you want to get that as well, then you will need a more complicated sed command or (easier, IMHO) a pipe of grep (providing the context, see the -A and -B options) and sed (above). Regards, Joerg That reminds me of the story of the mailing list maintainer who decided to replace 'and' with ''. He ended up sending mail to Sy Serson. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Michael, here's what I can tell you - Some people will advise you against storing documents in tables - claim it makes the data harder to serve up, etc. I agree it's quite simple to set up a folder structure with your files on the filesystem and point Apache or IIS to it while tracking metadata in MySQL, but there may well be other destinations/uses for your documents where actually having them in BLOBs would be quite handy. There's no technical reason you can't do it, obviously, and I worked for several years at a midsize newspaper where we stored literally everything in gigantic Sybase databases. Story, ad, page layouts, postscript graphics files, etc. Everything. And by and large it worked quite well. Nearly 1TB by the time I left, and a colleague at another newspaper had near 3TB. A big plus was the centralized workflow and tracking it allowed, but that will depend largely on the quality of the application software you have. At any rate - based on my experience with the Sybase system I managed, I would advise you to consider this when designing your database: instead of having one gigantic table to store every document, try to design a system that allows for splitting the data across multiple identical tables. You could do this with the MERGE engine in MySQL; that has MyISAM tables underneath, with a view of sorts that presents all the underlying tables as one. You could also do it by having multiple DocumentTable001 tables structured identically, with another table to track the document tables as well as the current insert path. This is obviously more complex but doable. MyISAM is not transactional; InnoDB is, but doesn't offer MERGE. InnoDB can be configured to store one file per table in the latest versions of MySQL, and I'd recommend you go that route. Having the data split across multiple table files (in MyISAM or InnoDB) will allow you to check, optimize, and on bad days recover, your data in a more incremental fashion than a single large table. It would also potentially allow you to distribute the data across multiple physical storage devices for improved speed - and while that may not be a concern up front, some day it likely will be if you intend to store things for long. You could even distribute data across multiple database servers or clusters if you structured it properly. You could also take advantage of MySQL's compressed table type for archival data, which would save disk space and potentially improve read speed if your data compresses well. Anyway, hope this helps. Let me know if I can answer any other questions about such a setup. Dan On 4/20/07, Michael Higgins [EMAIL PROTECTED] wrote: Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: advice for blob tables?
Here's a good php implementation, you can implement the concept in any language you like: http://www.dreamwerx.net/phpforum/?id=1 On Fri, 20 Apr 2007, Michael Higgins wrote: Hello, all -- I want to set up a database for document storage. I've never worked with binary files stored in tables. So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? I feel like I need to become a bit more expert in database design but I simply don't really know where to start. Like, what 'engine' should I use in this case? I haven't a clue Thanks! -- Michael Higgins -- 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: advice for blob tables?
This one time, at band camp, Michael Higgins [EMAIL PROTECTED] wrote: So before I just jump in and go like I usually do, does anyone have any quick advice, things to consider, links, must-RTFMs or the like to help a newbie out? This tutorial deals with images and BLOBs. It should get you on the right path. http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
