Re: need help with delete query
- Original Message - From: "Ben Liu" <[EMAIL PROTECTED]> To: Sent: Thursday, June 14, 2007 3:11 PM Subject: need help with delete query I'm trying to delete a subset of items in a table. The problem is, I don't want to query for the subset first, store the results and then run a series of delete queries. I believe this is a rather basic issue that has a well-accepted, simple solution which I am ignorant of. There are two relevant tables: order_items (individual items in a particular order) item_id ord_id orders (order details such as person's name, address, phone number, etc) ord_id cust_id I have set all "shopping carts" or initiated/incomplete orders so that orders.cust_id=-1 I wish to "clear all shopping carts" so I need to delete two sets of items: 1) All records in order_items where order_items.ord_id=orders.ord_id and orders.cust_id=-1 2) All records in orders where orders.cust_id=-1 I can do #2 easily enough, but I can't figure out how to write the delete query for #1. I've tried: DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND orders.cust_id=-1 DELETE FROM order_items LEFT JOIN orders ON order_items.ord_id=orders.ord_idWHERE orders.cust_id=-1 Neither of them seem to work. Thanks for any help. First of all, let me explain that I have been away from database work for about a year now and I have not kept up with developments in MySQL. However, I have been working with databases for many years, including many years with DB2 and a fair bit of work with MySQL until last summer. So, based on that experience, I'm going to offer you my opinions for what they're worth. First of all, the first DELETE that you cited should work. I don't see any reason why it wouldn't except possibly that there are no rows that satisfy the query. It should be easy to determine if there are any qualifying rows: simply turn the query into a SELECT and see if any rows satsify the query. Run: SELECT * FROM order_items WHERE order_items.ord_id=orders.ord_id AND orders.cust_id=-1 If you get no rows from that, then that's why your query failed. In that case, investigate the UDPATE queries that were supposed to be setting the cust_id to -1 and see what's wrong with them. As for the second DELETE, I am dubious that this could ever work but I'm not 100% certain. That query LOOKS like a join and, in DB2 at least, you can never delete rows from a join. Now, MySQL may tolerate that syntax and not interpret it as a join so you may want to confirm this with someone familiar with the version of MySQL you are using. So, in a nutshell, the first DELETE should work fine but you may not have the data there that will allow it to do anything. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump for myisam tables.
Hi All, What are the parameters that i need to use to take consistent backup of myisam tables using MYSQLDUMP. regards anandkl On 6/15/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: Hi All, I am taking mysqldump of myisam table for the first time on a production database. Can you please let me know what all necessary thing i need to take care before i start mysqldump. Its on a running database. Also please tell me what all important parameters i need to use in mysqldump. Thanks for your help regards anandkl
mysqldump for myisam tables.
Hi All, I am taking mysqldump of myisam table for the first time on a production database. Can you please let me know what all necessary thing i need to take care before i start mysqldump. Its on a running database. Also please tell me what all important parameters i need to use in mysqldump. Thanks for your help regards anandkl
Re: need help with delete query
Thanks Brent, good tip. Works like a charm. On Jun 14, 2007, at 7:42 PM, Brent Baisley wrote: Here's a little trick. Get your DELETE query working as a SELECT. Then replace everything before FROM with DELETE tablename. SELECT order_items.ord_id FROM order_items LEFT JOIN orders ON order_items.ord_id=orders.ord_id WHERE orders.cust_id=-1 ...becomes... DELETE order_items FROM order_items LEFT JOIN orders ON order_items.ord_id=orders.ord_id WHERE orders.cust_id=-1 You may have to tweak it a little, but the DELETE tablename FROM option is something many people miss. Although I'm pretty sure it's covered in the manual. On 6/14/07, Ben Liu <[EMAIL PROTECTED]> wrote: I'm trying to delete a subset of items in a table. The problem is, I don't want to query for the subset first, store the results and then run a series of delete queries. I believe this is a rather basic issue that has a well-accepted, simple solution which I am ignorant of. There are two relevant tables: order_items (individual items in a particular order) item_id ord_id orders (order details such as person's name, address, phone number, etc) ord_id cust_id I have set all "shopping carts" or initiated/incomplete orders so that orders.cust_id=-1 I wish to "clear all shopping carts" so I need to delete two sets of items: 1) All records in order_items where order_items.ord_id=orders.ord_id and orders.cust_id=-1 2) All records in orders where orders.cust_id=-1 I can do #2 easily enough, but I can't figure out how to write the delete query for #1. I've tried: DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND orders.cust_id=-1 DELETE FROM order_items LEFT JOIN orders ON order_items.ord_id=orders.ord_idWHERE orders.cust_id=-1 Neither of them seem to work. Thanks for any help. ~Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Thanks - that's what I thought. I really don't have much experience with mySQL. If it's not too much trouble, could someone give me a bit more help on how to do that please? Ed. no, those won't match based on just the datatype change.. you will have to define a user defined function to do those comparisons. On 6/14/07, Edward Quick <[EMAIL PROTECTED]> wrote: Thanks, that's interesting. Actually the uc.ID column is still type tinyint as it holds only one number, but are you saying if I change this to varchar my query will work e.g. 15 = 15:17 would work? > >What is the type of the 'uc.ID' column? If it's varchar, your match >will work fine. If it's an integer type, you are going to have a >problem because you have bt.category_ID holding things which can't be >represented as integers and will therefore never match. IF both >column type are being changed here, your query will work fine as is. > >- michael dykman > >On 6/14/07, Edward Quick <[EMAIL PROTECTED]> wrote: >>Hi, >> >>I have the following mySQL query in my script which has been working fine >>but due to a recent change, I had to modify one of the columns, >>bt.category_ID. This used to be defined as tinyint(3) but I've changed >>that >>now to varchar(20) as it needs to hold values such as 15, or 74:79 or >>43:56:113 >> >>In light of that, could anyone tell me what I need to change in my SQL to >>get it working please? >>Presuambly uc.ID=bt.category_ID won't work anymore. >> >>INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, >>url_query_ID, url_category_ID) >> SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, >>uc.ID >> FROM bulk_table bt >> INNER JOIN url_servers us ON us.server=bt.server >> INNER JOIN $pathstable up ON up.path=bt.path >> INNER JOIN url_schemes usc ON >>usc.ID=bt.scheme_ID >> INNER JOIN $queriestable uq ON uq.query=bt.query >> INNER JOIN url_categories uc ON >>uc.ID=bt.category_ID; >> >>Many thanks, >> >>Ed. >> >>_ >>Win tickets to the sold out Live Earth concert! >>http://liveearth.uk.msn.com >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> > > >-- >- michael dykman >- [EMAIL PROTECTED] > >- All models are wrong. Some models are useful. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Play your part in making history - Email Britain! http://www.emailbritain.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Thanks, that's interesting. Actually the uc.ID column is still type tinyint as it holds only one number, but are you saying if I change this to varchar my query will work e.g. 15 = 15:17 would work? What is the type of the 'uc.ID' column? If it's varchar, your match will work fine. If it's an integer type, you are going to have a problem because you have bt.category_ID holding things which can't be represented as integers and will therefore never match. IF both column type are being changed here, your query will work fine as is. - michael dykman On 6/14/07, Edward Quick <[EMAIL PROTECTED]> wrote: Hi, I have the following mySQL query in my script which has been working fine but due to a recent change, I had to modify one of the columns, bt.category_ID. This used to be defined as tinyint(3) but I've changed that now to varchar(20) as it needs to hold values such as 15, or 74:79 or 43:56:113 In light of that, could anyone tell me what I need to change in my SQL to get it working please? Presuambly uc.ID=bt.category_ID won't work anymore. INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, url_query_ID, url_category_ID) SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, uc.ID FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN $pathstable up ON up.path=bt.path INNER JOIN url_schemes usc ON usc.ID=bt.scheme_ID INNER JOIN $queriestable uq ON uq.query=bt.query INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Many thanks, Ed. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question
Hi, I have the following mySQL query in my script which has been working fine but due to a recent change, I had to modify one of the columns, bt.category_ID. This used to be defined as tinyint(3) but I've changed that now to varchar(20) as it needs to hold values such as 15, or 74:79 or 43:56:113 In light of that, could anyone tell me what I need to change in my SQL to get it working please? Presuambly uc.ID=bt.category_ID won't work anymore. INSERT IGNORE $visitstable (url_scheme_ID, url_server_ID, url_path_ID, url_query_ID, url_category_ID) SELECT DISTINCT usc.ID, us.ID, up.ID, uq.ID, uc.ID FROM bulk_table bt INNER JOIN url_servers us ON us.server=bt.server INNER JOIN $pathstable up ON up.path=bt.path INNER JOIN url_schemes usc ON usc.ID=bt.scheme_ID INNER JOIN $queriestable uq ON uq.query=bt.query INNER JOIN url_categories uc ON uc.ID=bt.category_ID; Many thanks, Ed. _ Win tickets to the sold out Live Earth concert! http://liveearth.uk.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Host through Heartbeat
The "\!" command is specific to the mysql client and executes locally, making it useless for this purpose. If you don't have access to 5.0.41+ and the database you are querying is a replicated slave, you can use the 'server_id' variable to tie the server to an actual hostname (SHOW VARIABLES LIKE 'server_id') as it is unique. Ben - just curious, why is heartbeat tied in with the server IP? Is it monitoring the server instead of the mysql daemon to check if it's up? :) Cheers, Atle On Wed, 13 Jun 2007, Scott Tanner wrote: > There's a 'report-host' option that can be set in the conf file to > mask the host name. Sounds like this may be set. > > If you want to get the server's actual host name from within mysql, > how about running a system command: >mysql> \! hostname; > >or > >mysql> \! cat /etc/hostnames; (debian) >mysql> \! cat /etc/hosts; (CentOS/rhel) > > > > Regards, > Scott > > > > On Wed, 2007-06-13 at 17:46 +0100, Ben Clewett wrote: > > What I know is that: > > > > Heartbeat with MySQL uses two IP's. That of the server, and that of the > > resource MySql. The former is fixed, the latter moves with MySQL when > > it's moved to another server. > > > > The one I need is the hostname of the physical server, not the resource. > > > > I've installed 5.0.41 and have found that the 'hostname' variable does > > report the hostname of the physical server. I have no idea how it does > > it :) > > > > I have my solution, thanks for the help, > > > > Ben Clewett. > > > > > > > > Baron Schwartz wrote: > > > Gerald L. Clark wrote: > > >> Baron Schwartz wrote: > > >>> Gerald L. Clark wrote: > > >>> > > Ben Clewett wrote: > > > > > Dear MySQL, > > > > > > I'm running 5.0.26 through Heartbeat. Which seems to work well, > > > even as a replication slave and Heartbeat continously stopping and > > > starting the server. > > > > > > The Heartbeat moves MySQL around from server to server when a > > > failure occures. I am trying to find a way for MySQL to report the > > > server host name on which it's currently sitting. Without any luck. > > > > > > Would any kind members know of a way of getting this information > > > from MySQL? > > > > > > Many thanks, > > > > > > Ben > > > > > > > > Heartbeat moves the IP address around as well as the services. > > Your hostname should not change. > > >>> > > >>> > > >>> DNS won't change, but the server's /etc/hostname will, right? > > >>> > > >>> Disclaimer: I'm no expert on this... I didn't even know the IP > > >>> address moved too. I should read about Heartbeat. > > >>> > > >>> Baron > > >> Do you actually have /etc/hostname? > > >> RHEL and Centos do not. > > >> They do have an entry in /etc/sysconfig/network > > > > > > Debian and Gentoo have /etc/hostname and /etc/conf.d/hostname, > > > respectively. I would think this is what /usr/bin/hostname uses, and > > > probably where the hostname server variable gets set from in MySQL 5.0.41. > > > > > > Baron > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Help Needed
Because you are a novice to data base design, you have fallen into a common trap. If you think about an array, you don't want to store multiple users in a row, you want to store them in a column. In other words, you want to have one table that stores businesses (once per business, probably) and another table that stores the associated users. You use a unique business ID to find all of the users for a particular business. Once you start thinking that way, you'll start to get the hang of it. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -Original Message- > From: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 14, 2007 1:50 AM > To: mysql@lists.mysql.com > Subject: Design Help Needed > > Hi, > > I'm creating an application for my web site. I want help in designing > database tables. Currently I'm starting with user management system. > > The web site would have these types of users > > 1. Customer account > 1a. Individual account. This user would be an individual > with username, > password, billing address, account security question, answer and few > more fields. > 1b. Business account. Each business account would have many users. > Currently I have not decided the number of users for this type of > account. It may be 10 users in the beginning. I want to keep > an option > to increase the number of users for business accounts. The business > account will have, business name, billing address, account security > question, answer, and few other business details. Each user > within the > account will have username, password, first name last name, mobile > number and other personal details. > 2. Partner account. These are similar to 1b business account type. > 3. Internal account. These are employee accounts. Each user will have > username, password, first name, last name, department, phone > number and > few other fields. > > Ideally how many tables should I create? What are the types of > keys(primary and foreign) > > Other modules of the application I would be developing in the future > are, contact management, shopping cart, mailing lists, > customer support, > etc. > > I have MySQL 4.1 on the server. Hope my question is clear. > > PS: I'm new to databases. > > > Thanks for the help, > Sudheer. S > Binary Vibes > > > > -- > 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]
need help with delete query
I'm trying to delete a subset of items in a table. The problem is, I don't want to query for the subset first, store the results and then run a series of delete queries. I believe this is a rather basic issue that has a well-accepted, simple solution which I am ignorant of. There are two relevant tables: order_items (individual items in a particular order) item_id ord_id orders (order details such as person's name, address, phone number, etc) ord_id cust_id I have set all "shopping carts" or initiated/incomplete orders so that orders.cust_id=-1 I wish to "clear all shopping carts" so I need to delete two sets of items: 1) All records in order_items where order_items.ord_id=orders.ord_id and orders.cust_id=-1 2) All records in orders where orders.cust_id=-1 I can do #2 easily enough, but I can't figure out how to write the delete query for #1. I've tried: DELETE FROM order_items WHERE order_items.ord_id=orders.ord_id AND orders.cust_id=-1 DELETE FROM order_items LEFT JOIN orders ON order_items.ord_id=orders.ord_idWHERE orders.cust_id=-1 Neither of them seem to work. Thanks for any help. ~Ben
Re: Creating a Cache field
At 10:22 AM 6/14/2007, Ins wrote: Hi, I have a MySQL database of thousands of external webpage links. I want to extract cache page for each and put the content in my database, so that I can display, just like google, a cached page for each weblink. How do I go about it? How to extract cache, how to store it and how to display it. What scripts are necessary? Ins Ins, MySQL automatically caches queries so subsequent queries to the same link are fetched from memory. This is automatic and you don't have to do anything except to adjust your query_cache_size to be large enough for the queries that are repetitive. (You may need to buy more RAM if you want a large query cache) I don't think there is any point caching links that are never going to be requested. See the tutorial at http://www.databasejournal.com/features/mysql/article.php/3110171 You could also put the entire table into a memory table but that will eat of memory. It may work ok for 25,000 rows, but if you exceed 1 million rows, it could be overkill and may not be faster than the query cache on a normal MyISAM table Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Request: Schema suggestion for items which change over time...
Hi, On Thu, June 14, 2007 18:16, Jake Peavy wrote: > Hi all, > > Can someone suggest a good method or normalized schema for storing product > information (id, description, price) which changes over time so that as a > product is gradually discounted, an order will reflect the cost of that > particular product at that particular time? > One method could be to store product(id, description, price) and orderitem(id,orderid,productid,quantity,price) This way your orders will reflect the item-price at the time the order was created. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating a Cache field
Hi, I have a MySQL database of thousands of external webpage links. I want to extract cache page for each and put the content in my database, so that I can display, just like google, a cached page for each weblink. How do I go about it? How to extract cache, how to store it and how to display it. What scripts are necessary? Ins -- View this message in context: http://www.nabble.com/Creating-a-Cache-field-tf3922833.html#a11123690 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Request: Schema suggestion for items which change over time...
Hi all, Can someone suggest a good method or normalized schema for storing product information (id, description, price) which changes over time so that as a product is gradually discounted, an order will reflect the cost of that particular product at that particular time? -- -jp At birth, Chuck Norris came out feet first so he could roundhouse kick the doctor in the face. Nobody delivers Chuck Norris but Chuck Norris
RE: Design Help Needed
Hi Sudheer, First of all there a number of ways to design this database. You will need to choose the one that you feel suites your needs best. Here one possible design. Because you have different type of users/accounts, it looks like "ACCOUNT_TYPE" table is needed ACCOUNT_TYPE table Account_type_id - autoincrement, PK Account_type - varchar(25). ( Values in this field are: "Individual","Business","Partner","Internal") Because you have user information to store: USER_INFO table User_id - autoincrement, PK Account_type_id - integer, FK (foreign key to ACCOUNT_TYPE) Fields of personal/Business information to follow - (First, Last Name, .) Because you have web site security info to store: WEB_SITE_SECURITY_INFO table User_id - integer, FK (foreign key to USER_INFO) Security_question Security_question_answer Fields of security information to follow You can grow the database as you add module, but as much as you can foresee and design ahead it would be better Mikhail Berman -Original Message- From: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 1:50 AM To: mysql@lists.mysql.com Subject: Design Help Needed Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- 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: Before I shoot myself in the foot...
> You mean stored procs and functions are not dependent on tables? They can, but not "have to" use tables. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com > On 6/14/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: > > > > Stored procedures and functions are not associated with tables. Triggers > > are, and I > > don't know about those. > > > > Ananda Kumar wrote: > > > Hi All, > > > Will the rename of table from y to x cause for all the stored procs, > > > functions on this table to be come invalid? > > > > > > regards > > > anandkl > > > > > > > > > On 6/14/07, Brian Dunning <[EMAIL PROTECTED]> wrote: > > >> > > >> Many thanks to all who took the time to reply. :) :) > > >> > > >> -- > > >> MySQL General Mailing List > > >> For list archives: http://lists.mysql.com/mysql > > >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > >> > > >> > > > > > > > -- > > 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]
Re: Before I shoot myself in the foot...
> You mean stored procs and functions are not dependent on tables? They can, but not "have to" use tables. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com > On 6/14/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: > > > > Stored procedures and functions are not associated with tables. Triggers > > are, and I > > don't know about those. > > > > Ananda Kumar wrote: > > > Hi All, > > > Will the rename of table from y to x cause for all the stored procs, > > > functions on this table to be come invalid? > > > > > > regards > > > anandkl > > > > > > > > > On 6/14/07, Brian Dunning <[EMAIL PROTECTED]> wrote: > > >> > > >> Many thanks to all who took the time to reply. :) :) > > >> > > >> -- > > >> MySQL General Mailing List > > >> For list archives: http://lists.mysql.com/mysql > > >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > >> > > >> > > > > > > > -- > > 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]
Re: Before I shoot myself in the foot...
You mean stored procs and functions are not dependent on tables? On 6/14/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: Stored procedures and functions are not associated with tables. Triggers are, and I don't know about those. Ananda Kumar wrote: > Hi All, > Will the rename of table from y to x cause for all the stored procs, > functions on this table to be come invalid? > > regards > anandkl > > > On 6/14/07, Brian Dunning <[EMAIL PROTECTED]> wrote: >> >> Many thanks to all who took the time to reply. :) :) >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> > -- Baron Schwartz http://www.xaprb.com/
Re: Before I shoot myself in the foot...
Stored procedures and functions are not associated with tables. Triggers are, and I don't know about those. Ananda Kumar wrote: Hi All, Will the rename of table from y to x cause for all the stored procs, functions on this table to be come invalid? regards anandkl On 6/14/07, Brian Dunning <[EMAIL PROTECTED]> wrote: Many thanks to all who took the time to reply. :) :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Re: How can I do something like this "SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3);" ?
2007/6/13, Ricardas S <[EMAIL PROTECTED]>: ops again you probably needed just select greatest(col1,col2,col3) from t order by 1 Thanks, it's work very well. best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Help Needed
HI Sudheer, THIS DESIGN IS BASED ON MY MANY->TO->ONE DIRECTION PATTERN OF DATABASE NORMALIZATION DESIGN... PLEASE MODIFY/CORRECT IT ACCORDING TO YOUR TASTE. AS A JAVA DEVELOPER I'M USING HIBERNATE FOR MY CREATE-UPDATE-DELETE(CUD) AND DIRECT JDBC FOR MY QUERIES(R). YOU MAY VARY. HERE, HOPE THIS WILL GIVE YOU AN IDEA. LEGEND: X-TABLE, L-LONG, S-STRING/CHAR(?), T-DATETIME _ID-PRIMARY_KEY, REF-FOREIGN_KEY X:CONTACTS_AND_ADDRESSES L:CONTACT_AND_ADDRESS_ID S:WEBSITE S:EMAIL S:MESSAGING S:TELEPHONE S:MOBILE S:LAND_ADDRESS T:CREATED/MODIFIED X:INDIVIDUALS L:INDIVIDUAL_ID S:USERNAME S:PASSWORD S:DISPLAY_NAME S:PICTURE_URI S:SECURITY_QUESTION S:ANSWER T:CREATED/MODIFIED X:INDIVIDUALS_CONTACTS_AND_ADDRESSES L:INDIVIDUAL_CONTACT_AND_ADDRESS_ID L:REF_INDIVIDUAL_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS L:ESTABLISHMENT_ID S:BUSINESS_NAME T:CREATED/MODIFIED X:ESTABLISHMENTS_CONTACT_AND_ADDRESS L:ESTABLISHMENT_CONTACT_AND_ADDRESS_ID L:REF_ESTABLISHMENT_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS_OF_INDIVIDUALS L:ESTABLISHMENT_OF_INDIVIDUAL_ID L:REF_ESTABLISHMENT_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:CUSTOMERS L:CUSTOMER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:PARTNERS L:PARTNER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:EMPLOYEES_POSITIONS L:EMPLOYEE_POSITION_ID S:ROLE S:SPECIFIC_RULE S:HOWTODO T:CREATED/MODIFIED X:TIME_SCHEDULES L:TIME_SCHEDULE_ID S:APPLIED_TASK T:APPLIED_TIME L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED X:EMPLOYEES L:EMPLOYEE_ID L:REF_INDIVIDUAL_ID L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED GOOD LUCK, HOPE THIS HELPS. -Melvin Sudheer Satyanarayana <[EMAIL PROTECTED]> wrote: Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
