mysql - 如何根据 WHERE 和 AND 更新 2 个不同的行

Question

我想更新一个表，并且取决于位置和 AND 取决于我要更新的行，所以如果 message_to 和 message_id 匹配更新 message_deleted_to 或如果 message_user、message_id 匹配它们更新 messaged_deleted_from

我的 table :

CREATE TABLE IF NOT EXISTS `messages` (
  `message_id` int(11) NOT NULL,
  `message_user` int(11) NOT NULL,
  `message_subject` varchar(100) NOT NULL,
  `message_body` text NOT NULL,
  `message_to` int(11) NOT NULL,
  `message_from_read` int(1) NOT NULL DEFAULT '1',
  `message_to_read` int(11) NOT NULL DEFAULT '0',
  `message_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `message_deleted_from` int(11) NOT NULL DEFAULT '0',
  `message_deleted_to` int(11) NOT NULL,
  `message_permdeleted` int(11) NOT NULL DEFAULT '0',
  `message_type` varchar(11) NOT NULL,
  `message_deleted` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` (`message_id`, `message_user`, `message_subject`, `message_body`, `message_to`, `message_from_read`, `message_to_read`, `message_date`, `message_deleted_from`, `message_deleted_to`, `message_permdeleted`, `message_type`, `message_deleted`) VALUES
(6, 12, 'lol', 'lol', 1, 1, 0, '2015-12-14 04:54:32', 0, 0, 0, 'sent', 0),

我想完成的事情(我知道这段代码行不通，我只是想告诉你我想做什么)

UPDATE messages 
    SET message_deleted_from = 1 
    WHERE message_user = ? 
    AND message_id = ?

    OR SET messages_delete_to = 1
    WHERE message_to
    AND message_id = ?

Answer 1

你可以试试这个:

UPDATE messages 
    SET message_deleted_from =  
    CASE WHEN message_user = ? 
    AND message_id = ? THEN 1 ELSE message_deleted_from END,

    messages_delete_to = 
    CASE WHEN message_to=? 
    AND message_id = ? THEN 1 ELSE messages_delete_to END