Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Basically i have a notification table like so -
code:
+ 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 +
And I'm trying to UPDATE a row only if a date value is greater than the current date field value and WHERE the staff_id and location equals a particular value BUT INSERT the row if it doesn't exist.
Usually you would use -
code:
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 something like this -
code:
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 obviously that doesn't work and I'm having one serious brain fart trying to figure out a solution without using multiple queries. Any help will be grateful 
p.s - granted there will be a better way of doing these notifications but this is what I'm stuck with unfortunately.
[Edited on 05-12-2011 by Dom]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Not clear on the problem, you want to test on date and run a different query on the outcome?
If so there is no date in the condition of your IF()?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
On the query I posted the data check is on the UPDATE statement. But yes, test on the date where staff_id = X and location = Y and UPDATE otherwise INSERT.
Basically -
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.
[Edited on 05-12-2011 by Dom]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
So what is wrong with that IF() code?
Looks like it should work I think?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Yup, i thought it'd work but it throws back SQL syntax errors.
The only solution I can think of is to query (and test the date) the table prior to updating or inserting but ideally I want it to be a single query.
Will have to have a read through the MySQL documentation again and see what i can find. Cheers though
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
What is the syntax error?
You can test the date in the UPDATE, WHERE clause will see to that.
Do the UPDATE and INSERT lines work independently?
Also does the IF() work if you put in two SELECT * FROM notifications LIMIT 1 just to test it?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Get a #1064 error.
And yes, both UPDATE and INSERT lines work independently, was one of the first things i checked to make sure i wasn't being a pleb 
But as thought, it's failing on the IF Statement as from what I remember it's being used incorrectly outside of a statement.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
So what happens when you run the IF() with an easy SELECT in each part?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Ian
So what happens when you run the IF() with an easy SELECT in each part?
Fails on a 1064 error. Tried bracketing each statement just on the off chance but that fails as well.
This is using MySQL 5.0.92 although I don't think latter versions will make any difference; although i double check this on 5.5 tomorrow.
[Edited on 06-12-2011 by Dom]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
And what about that SELECT COUNT... on its own? Returns a value I assume?
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Just thinking - that is not an inequality so you can't >0 on it.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Yup, the SELECT COUNT(*) works fine.
Edit - Shouldn't '(SELECT COUNT(*) FROM `notifications` WHERE `staff_id` = '6' AND `location` = 'Bedfordshire')' at least return 'TRUE' though?
[Edited on 06-12-2011 by Dom]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
It'll always be true as you'll always get rows, even if the resulting count is zero.
Or you might get empty set? Not sure. Either way if you're using an aggregate function you would always expect a row, just the data to differ. Not the same as running a regular query and waiting for rows or a empty set.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
mysql> select count(*) from codes where serial = 'h';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)
Yeah - row returned even though the count is zero, that will evaluate to true which is incorrect.
That doesn't solve the syntax issue though.
|
Lawrah
Premium Member
Registered: 25th Dec 04
User status: Offline
|
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Sorted -
code:
SELECT IF(EXISTS (SELECT * FROM `notifications` WHERE `Staff_ID` = '6' AND `Location` = 'Bedfordshire'), 'Yes','No')
Edit - Nevermind, that fails being a SELECT statement  Got excited then
[Edited on 06-12-2011 by Dom]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
code:
mysql> select if(exists(select * from codes where serial = 'g' limit 1),1,0) as
test;
+------+
| test |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> select if(exists(select * from codes where serial = '123456' limit 1),1,0
) as test;
+------+
| test |
+------+
| 1 |
+------+
1 row in set (0.02 sec)
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Problem is that'll return the two states as output, not run another statement.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
What is the great reason you can't do the conditional in software?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Ian
What is the great reason you can't do the conditional in software?
Was thinking performance but it looks to be the only viable solution (read - it's a load of BS and I'm going to give up)
Cheers for the help though, much appreciated
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
They're three distinct operations anyway so there's not much of a performance hit doing the logic elsewhere.
Just make sure the individual queries are using indexes properly, think you'll need a different one for each of the SELECT and the UPDATE because the UPDATE has the additional date clause.
Might also be that you can rewrite it so that the UPDATE uses a variable populated from the SELECT rather than the larger WHERE clause inside it, ie. SELECT locates a key in a field somewhere so the UPDATE doesn't have to duplicate that work by looking back across other columns.
Edit - although that date thing on the UPDATE does differentiate the queries, there's probably not a way of getting a decision in your first SELECT that doesn't include the date then doing an UPDATE that does without it being two passes on the table.
[Edited on 06-12-2011 by Ian]
|
