Re: conditional updating
On 2/9/2012 8:58 AM, Reindl Harald wrote: Am 09.02.2012 14:55, schrieb william drescher: On 2/9/2012 8:22 AM, Johnny Withers wrote: Update table set mydate=now() where mydate='-00-00'; should do it. can't do that because the record is selected by other criteria. so explain the criteria, show us the query usually you do exatcly the same WHERE as for the select and add and mydate='-00-00' The query was to find a record by a unique ID, then update last access to now, and then update first access date to curdate() if it is -00-00. So, I obviously can not select the record based on first access as I need to update it regardless of the value of first access. IF() works like a charm. bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Inaccurate return value from DELETE query
CREATE TABLE `People` ( `friday_id` bigint(20) NOT NULL, `parent_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`friday_id`), KEY `parent_id` (`parent_id`), CONSTRAINT `People_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `People` (`friday_id`) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO People (friday_id, parent_id) values (1,1), (2,1); DELETE FROM People; Output: Query OK, 1 row affected Shouldn't this be 2 rows affected? MySQL version 5.1 -- Fayaz Yusuf Khan Cloud developer and architect Dexetra SS, Bangalore, India fayaz.yusuf.khan_AT_gmail_DOT_com fayaz_AT_dexetra_DOT_com +91-9746-830-823 signature.asc Description: This is a digitally signed message part.
Re: Inaccurate return value from DELETE query
I think it's because of the constraint that you've defined on the table. So that the record with friday_id=1 won't be deleted because of the constraint. Sent from my BlackBerry® smartphone from Sinyal Bagus XL, Nyambung Teruuusss...! -Original Message- From: Fayaz Yusuf Khan fa...@dexetra.com Date: Sat, 11 Feb 2012 22:03:45 To: mysql@lists.mysql.com Subject: Inaccurate return value from DELETE query CREATE TABLE `People` ( `friday_id` bigint(20) NOT NULL, `parent_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`friday_id`), KEY `parent_id` (`parent_id`), CONSTRAINT `People_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `People` (`friday_id`) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO People (friday_id, parent_id) values (1,1), (2,1); DELETE FROM People; Output: Query OK, 1 row affected Shouldn't this be 2 rows affected? MySQL version 5.1 -- Fayaz Yusuf Khan Cloud developer and architect Dexetra SS, Bangalore, India fayaz.yusuf.khan_AT_gmail_DOT_com fayaz_AT_dexetra_DOT_com +91-9746-830-823