MySQL Query Woes

[visibleman]

I'm having a serious brain fart over this but essentially i have a table that looks similar to this -

+  id  +  staff_id   +    location       +    date    + dismiss_boolean + 
+------+-------------+-------------------+------------+-----------------+
+   1  +      22     + Bedfordshire      + 2011-11-01 +           0     +
+   2  +      22     + Hertfordshire     + 2011-11-02 +           1     +
+   3  +      16     + Bedfordshire      + 2011-12-01 +           0     +
+   4  +      17     + Bedfordshire      + 2011-11-22 +           0     +
+   5  +      77     + Hertfordshire     + 2011-11-01 +           1     +
+   6  +      77     + Cambridgeshire    + 2011-11-01 +           1     +

What i'm after is (in a single query) -

If the row exists, ie: there is a row where staff_id = 6 and location = Bedfordshire, then UPDATE the row only if the date field is older than X date.

Otherwise, if the row doesn't exist (there isn't a row where staff_id = 6 and location = Bedfordshire) then INSERT it.


Usually you would use -

INSERT....ON DUPLICATE KEY UPDATE...

But you can't, iirc, use WHERE clauses in the UPDATE statement if using ON DUPLICATE. And again you can't use UNIQUE Indexes on the location and staff_id fields due to duplicates.

So i'm after a query along the lines of -

IF(
    (SELECT COUNT(*) FROM `notifications` WHERE `staff_id` = '6' AND `location` = 'Bedfordshire') > 0
,
    UPDATE `notifications` SET `dismiss_boolean` = '1', `date` = '2011-12-10'  WHERE `staff_id` = '6' AND `location` = 'Bedfordshire' AND `date` < '2011-12-10'
,
    INSERT INTO `notifications` (`staff_id`, `location`, `date`, `dismiss_boolean`) VALUES ('6', 'Bedfordshire', '2011-12-10', '1')
)

But that throws syntax errors and it's incorrect use of the IF function anyways (iirc, IF function can only be used within conditions/clauses etc).


So has anyone got any ideas how i can accomplish this? The only solution i can think of is to query the table prior to updating or insert the data but as said, ideally i want to do this in a single query.

Cheers for any help

 

[PsychoDuck]

I am not sure if this is what you are after:-

if exists(SELECT id FROM notifications WHERE staff_id = 6 AND location = 'Bedfordshire')
begin
	UPDATE notifications SET dismiss_boolean = 1, date = '10-DEC-2011' WHERE staff_id = 6 AND location = 'Bedfordshire' AND date < '10-DEC-2011'
end
else
begin
	INSERT INTO notifications (staff_id, location, date, dismiss_boolean) VALUES (6, 'Bedfordshire', '10-DEC-2011', 1)
end

 

[topdog@OC]

And again you can't use UNIQUE Indexes on the location and staff_id fields due to duplicates.

Only if those duplicates are in both columns combined, for multiple rows.

In your table sample, for example, there are no duplicates that would prevent a (single) unique index being created against both of those columns. You'd be creating what's called a 'compound key' index, and it would serve to protect against the same staff_id being present at the same location. But you could certainly have the same staff_id or same location value present more than once throughout the rows.