Hi - I'm using mySQL v 4.1.22, so it should be ok to use DATEDIFF
This is my table description:
CREATE TABLE `spots` (
`SpotId` int(11) NOT NULL auto_increment,
`WidgetUUID` varchar(255) NOT NULL default '',
`Approved` char(1) NOT NULL default '0',
`ApprovedDate` datetime default NULL,
`SpotImage` varchar(255) default NULL,
`SpotLink` varchar(255) default NULL,
`SpotLinkText` varchar(50) default NULL,
`SpotMessage` varchar(255) default NULL,
`SpotEmail` varchar(255) default NULL,
`Active` char(1) NOT NULL default '1',
`SpotCost` double NOT NULL default '0',
`Paid` char(1) NOT NULL default '0',
`PaymentFee` double unsigned NOT NULL default '0',
`PaymentType` varchar(50) default NULL,
`TransactionId` varchar(255) default NULL,
`BuyerName` varchar(255) default NULL,
`SpotReimbursed` char(1) NOT NULL default '0',
`LastModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`SpotId`),
UNIQUE KEY `SpotId` (`SpotId`),
KEY `SpotId_2` (`SpotId`),
KEY `WidgetUUID_idx` (`WidgetUUID`(8))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='';
This query returns rows:
select spotId from spots
where spots.Active = 1
and spots.WidgetUUID IN(select distinct WidgetUUID from widgets where widgets.StayAlive = 'monthly')
and datediff(current_date, spots.LastModified) > 30;
However, this update statement affects 0 rows where I'd expect it would return the same count as above:
update spots set spots.Active = 0
where spots.Active = 1
and spots.WidgetUUID IN(select distinct WidgetUUID from widgets where widgets.StayAlive = 'monthly')
and datediff(current_date, spots.LastModified) > 30;
Can someone tell me what is wrong and why?
Thanks in advance.
Start Free Trial
