RE: Deleting records using the 'LIMIT' clause
Careful! I should clarify ... before you do the delete, make sure the columns really do uniquely identify that row with > select * from table where col2='some value' and col4='some other value' ...without the limit clause. Delete will delete everything that matches the where clause! Mike > -Original Message- > From: Michael Pheasant [mailto:[EMAIL PROTECTED] > Sent: Sunday, 4 April 2004 5:05 PM > To: [EMAIL PROTECTED] > Subject: RE: Deleting records using the 'LIMIT' clause > > > Hi, > > You need to delete using a where clause and column values unique to > that row. Try "SELECT * from table limit 10,1" see if you can > find some > value(s) which is unique to that record (irrelevant whether > its defined as a primary key). Eg, maybe col2 and col4 can > uniquely id > The record you want to nuke so you can then do: > > > delete from table where col2='some value' and col4='some > other value' > > The order of rows is undefined unless you use an order by > clause. I don't know if there is a way to delete the '11th' record as > determined by select. > > Cheers, > > M > > > -Original Message- > > From: Ross Honniball [mailto:[EMAIL PROTECTED] > > Sent: Sunday, 4 April 2004 10:40 AM > > To: [EMAIL PROTECTED] > > Subject: Deleting records using the 'LIMIT' clause > > > > > > Hi all, > > > > I have positively identified the row I want to delete using: > > > > 'SELECT * FROM table LIMIT 10,1' > > > > This has returned 1 record and I now want to DELETE the record. > > > > How do I identify this record in my DELETE statement? > > > > (using 'DELETE FROM table LIMIT 10,1' does not work) > > > > NOTE : I can't identify it using it's key fields as the > table has no > > primary key. > > > > Help greatly appreciated. > > > > Regards ... Ross > > > > . Ross Honniball JCU Bookshop Cairns Supervisor > > . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia > > . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] > > . There are no problems. Only solutions. > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting records using the 'LIMIT' clause
Hi, You need to delete using a where clause and column values unique to that row. Try "SELECT * from table limit 10,1" see if you can find some value(s) which is unique to that record (irrelevant whether its defined as a primary key). Eg, maybe col2 and col4 can uniquely id The record you want to nuke so you can then do: > delete from table where col2='some value' and col4='some other value' The order of rows is undefined unless you use an order by clause. I don't know if there is a way to delete the '11th' record as determined by select. Cheers, M > -Original Message- > From: Ross Honniball [mailto:[EMAIL PROTECTED] > Sent: Sunday, 4 April 2004 10:40 AM > To: [EMAIL PROTECTED] > Subject: Deleting records using the 'LIMIT' clause > > > Hi all, > > I have positively identified the row I want to delete using: > > 'SELECT * FROM table LIMIT 10,1' > > This has returned 1 record and I now want to DELETE the record. > > How do I identify this record in my DELETE statement? > > (using 'DELETE FROM table LIMIT 10,1' does not work) > > NOTE : I can't identify it using it's key fields as the table has no > primary key. > > Help greatly appreciated. > > Regards ... Ross > > . Ross Honniball JCU Bookshop Cairns Supervisor > . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia > . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] > . There are no problems. Only solutions. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: can't call a stored proc from another stored proc?
Sorry, I spoke too soon. I just made a test case of a proc calling a proc, which works, so I must have a different problem. - Still would like to know if a function will ever be able to issue a SELECT tho. Cheers Mike -Original Message- From: Michael Pheasant [mailto:[EMAIL PROTECTED] Sent: Saturday, 3 April 2004 8:57 PM To: [EMAIL PROTECTED] Subject: can't call a stored proc from another stored proc? Hi, 1) Can a stored procedure call another stored procedure? Ie, can you do 'call someproc()' from within a stored procedure? 2) Also, will a function ever be able to issue a SELECT query? I am using mysql-5.0.1-alpha (built froms ource) , winXP & win2k. The mysql daemon crashes without an error message when I try (1) Cheers, Mike -- 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]
can't call a stored proc from another stored proc?
Hi, 1) Can a stored procedure call another stored procedure? Ie, can you do 'call someproc()' from within a stored procedure? 2) Also, will a function ever be able to issue a SELECT query? I am using mysql-5.0.1-alpha (built froms ource) , winXP & win2k. The mysql daemon crashes without an error message when I try (1) Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CURDATE() bug?
I would say that the CURDATE is converted to an integer because of +0. Since if you add 1 to a date, should that increment years/months/days/ or seconds ? Use the DATE_ADD(date,INTERVAL expr type) functions instead. cheers mike -Original Message- From: Alan Williamson [mailto:[EMAIL PROTECTED] Sent: Wednesday, 31 March 2004 9:47 PM To: [EMAIL PROTECTED] Subject: CURDATE() bug? Could anyone tell me if this is a bug or not. SQL: SELECT CURDATE()+0; RESULT: 20040331 Thats good. However consider this: SQL: SELECT CURDATE()+1; RESULT: 20040332 Not so good. Infact with this version any WHERE clauses you would put this in, fails to bring back the right result. Does CURDATE() support numeric addition like this? Or is the "+0" purely a casting-hack to get the right format. Its not meant as pure addition. Thoughts? thanks alan -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- 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]
select statements inside functions?
Hi, I would like to know if functions will ever be able to issue SELECT statements, like procedures can. I want a function which can select multiple rows from another table, process those rows into a single scalar, and return that as its result. I want a function to do this so that I can use it in the column list of SELECT queries, rather than calling it like a procedure. A trivial example, showing the error saying I cant use SELECT in a function is below. - Will this be included in future, and is just missing now as functions are so new? I am using mysql 5.0.1-alpha (win32) Thanks, Mike. mysql> delimiter | mysql> create function test() -> returns int -> select 1234; -> end -> | ERROR 1301 (0A000): Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]