3

when i try to select and update the same table mysql gives error

error
#1241 - Operand should contain 1 column(s)

The trigger is

DELIMITER $$
CREATE TRIGGER visitor_validation
BEFORE INSERT ON ratingsvisitors
FOR EACH ROW
BEGIN
SET @ifexists = (SELECT * FROM ratingcounttracks WHERE userid=New.vistorid AND likedate=New.likevalidation AND countfor=New.likeordislike);
IF (@ifexists = NULL) THEN
INSERT INTO  ratingcounttracks(userid, likedate, clickcount,countfor) values (New.vistorid, New.likevalidation ,'1',New.likeordislike);
ELSE
UPDATE ratingcounttracks SET clickcount=clickcount+1 WHERE userid=New.vistorid AND likedate=New.likevalidation AND countfor=New.likeordislike;
END IF;
END$$
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Bipin Chandra Tripathi
  • 2,550
  • 4
  • 28
  • 45
  • What you are trying to do is called *UPSERT*: "UPdate if exists, inSERT otherwise". You might want to learn more about it, as there are different approaches to this. There are questions about upsert in MySQL on this site. You could start with [this one](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql "How do I update if exists, insert if not (aka upsert or merge) in MySQL?"), then follow those in its *Linked* section. – Andriy M Jun 03 '12 at 04:49

1 Answers1

1

The problem is you are selecting multiple columns (SELECT * FROM ...) into a single variable (@ifexists).

The solution is not to use a variable! Just use normal SQL IF NOT EXISTS (...):

DELIMITER $$
CREATE TRIGGER visitor_validation
BEFORE INSERT ON ratingsvisitors
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT * FROM ratingcounttracks WHERE userid=New.vistorid AND likedate=New.likevalidation AND countfor=New.likeordislike) THEN
    INSERT INTO  ratingcounttracks(userid, likedate, clickcount,countfor) values (New.vistorid, New.likevalidation ,'1',New.likeordislike);
ELSE
    UPDATE ratingcounttracks SET clickcount=clickcount+1 WHERE userid=New.vistorid AND likedate=New.likevalidation AND countfor=New.likeordislike;
END IF;
END$$
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Or even `if not exists (select 1 from ...)`. – mu is too short Jun 03 '12 at 04:48
  • @muistooshort Sure. It would be *very* slightly more efficient. In general (as I'm sure you do too) I try to make little change to the posted code so the OP can see the change and understand it. Unless the code is so bad nothing is worth salvaging, then I post a rewrite. – Bohemian Jun 03 '12 at 05:00
  • I'm not criticizing, just noting a possible alternative. How far I'd go would depend on how professorial I'm feeling at the time :) – mu is too short Jun 03 '12 at 05:08