Balaji S Balaji S - 1 year ago 53
MySQL Question

MySQL Delete on date and time comparision

CREATE TABLE `appointments` (
`id` int(11) NOT NULL,
`app_date` date NOT NULL,
`mobile` varchar(16) NOT NULL,
`time` time NOT NULL,
`duration` int(3) NOT NULL,
`services` varchar(256) NOT NULL,
`status` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `appointments` (`id`, `app_date`, `mobile`, `time`, `duration`,
`services`, `status`) VALUES
(11, '2017-09-28', '991694', '11:00:00', 1, 'PP', 'Booked'),
(12, '2017-09-28', '991694', '12:00:00', 1, 'FT', 'Booked'),
(13, '2017-09-29', '991694', '10:00:00', 1, 'HC;FT', 'Booked'),
(14, '2017-09-11', '991694', '11:00:00', 1, 'T;W;HS;PP', 'Booked');

DELETE from appointments where 'mobile' = '991694' and 'app_date' =
'2017-09-28' and 'time' = '11:00:00'


is not deleting any row? returning 0 rows affected. Please suggest what is going wrong here.

Also i tried with
DELETE from appointments where 'mobile' = '991694' and 'app_date' = date '2017-09-28' and 'time' = time '11:00:00'

Answer Source

Remove the quote before the column name. You ARE NOT checking the values on the rows, you are COMPARING STRINGS. Please notice that, in mysql

' != `

This should work:

DELETE from appointments where mobile = 991694 and app_date = '2017-09-28' and time = '11:00:00'

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download