Question

I WANT TO DOSOMETHING LIKE THIS:

CREATE TRIGGER addwinner AFTER INSERT ON bids
FOR EACH ROW BEGIN 
IF exists(select * from wins as LT where LT.item_index=NEW.item_index) THEN

SELECT item_index, MIN( bid_amount ) 
FROM update_winnerslist AS a1
WHERE a1.item_index =  NEW.item_index;
UPDATE wins SET email_id=NEW.emai_id, bid_amount=a1.bid_amount where wins.item_index=a1.item_index;

END IF;
END;

基本上,我想通过比较某些属性来更新一个表格。

Answer 1

如以下解释:,在手册中,在确定使用<代码的程序/触发/etc时;,在本机构内单独指挥,你需要界定替代物,以便 CREATE 指挥被解读为单一声明。

<>UPDATE with the results of SlectT, 如您所述,你可以做以下任何工作:

Save the results of the SlectT in a/2007/5 which they subsequently used in the UPDATE:

DELIMITER ;; -- or anything else you like

CREATE TRIGGER addwinner AFTER INSERT ON bids FOR EACH ROW
  IF EXISTS (SELECT * FROM wins AS LT WHERE LT.item_index=NEW.item_index)
  THEN
    SELECT MIN(bid_amount) INTO @bid_amount
    FROM   update_winnerslist AS a1
    WHERE  a1.item_index = NEW.item_index;

    UPDATE wins
    SET    email_id=NEW.emai_id, bid_amount=@bid_amount
    WHERE  wins.item_index=a1.item_index;
  END IF;;  -- whatever command delimiter you chose above goes here

DELIMITER ; -- reset to normal

仅使用一个子序列(只要它没有提及你重新更新的表格):

DELIMITER ;; -- or anything else you like

CREATE TRIGGER addwinner AFTER INSERT ON bids FOR EACH ROW
  IF EXISTS (SELECT * FROM wins AS LT WHERE LT.item_index=NEW.item_index)
  THEN
    UPDATE wins
    SET    wins.email_id=NEW.emai_id, wins.bid_amount=(
      SELECT MIN(bid_amount)
      FROM update_winnerslist AS a1
      WHERE a1.item_index = NEW.item_index
    )
    WHERE  item_index = NEW.item_index
  END IF;;  -- whatever command delimiter you chose above goes here

DELIMITER ; -- reset to normal

或只是加入表格:

DELIMITER ;; -- or anything else you like

CREATE TRIGGER addwinner AFTER INSERT ON bids FOR EACH ROW
  IF EXISTS (SELECT * FROM wins AS LT WHERE LT.item_index=NEW.item_index)
  THEN
    UPDATE wins JOIN update_winnerslist AS a1 USING (item_index)
    SET    wins.email_id=NEW.emai_id, wins.bid_amount=MIN(a1.bid_amount);
    WHERE  item_index = NEW.item_index
  END IF;;  -- whatever command delimiter you chose above goes here

DELIMITER ; -- reset to normal

友情链接