sql替换两个外部字段

68de4m5k  于 2021-06-18  发布在  Mysql
关注(0)|答案(4)|浏览(331)

当表中存在非主字段(user1和user2)的组合时,我需要替换“text”。或者插入新行user1,user2,text-如果没有。

CREATE TABLE IF NOT EXISTS opinions 
(id INT AUTO_INCREMENT PRIMARY KEY, 
user1 INT, user2 INT, text TEXT);

+----+-----------+-----------+----------+
| id | user1     | user2     | text     |
+----+-----------+-----------+----------+
|  1 | 141015727 | 627964361 | nice guy |
|  2 | 141015727 | 375392538 | hello    |
+----+-----------+-----------+----------+

预期产量:

+----+-----------+-----------+----------+
| id | user1     | user2     | text     |
+----+-----------+-----------+----------+
|  1 | 141015727 | 627964361 | bad guy  |
|  2 | 141015727 | 375392538 | hello    |
+----+-----------+-----------+----------+

升级版
mysql>从意见中选择*;

+----+-----------+-----------+--------+
| id | user1     | user2     | text   |
+----+-----------+-----------+--------+
|  1 | 141015727 | 627964361 | hello1 |
+----+-----------+-----------+--------+

预期产量

+----+-----------+-----------+--------+
| id | user1     | user2     | text   |
+----+-----------+-----------+--------+
|  1 | 141015727 | 627964361 | hello2 |
+----+-----------+-----------+--------+

已尝试(抱歉,似乎是我错误地删除了此答案)

IF EXISTS(SELECT user1, user2 FROM opinions WHERE user1=\'141015727\' 
AND user2 = \'627964361\') THEN UPDATE opinions SET text = \'hello2\' 
WHERE user1 = \'141015727\' AND user2 = \'627964361\' 
ELSE INSERT INTO opinions (user1, user2, text) 
VALUES (\'141015727\', \'627964361\', \'hello2\') END IF;'

有个错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT user1, user2 FROM opinions WHERE user1='141015727' AND user2 = '
6ojccjat

6ojccjat1#

where条件不在主键上。很遗憾,您不能在1个查询中执行。
请参阅本文:mysql upsert without on duplicate key

6g8kf2rb

6g8kf2rb2#

如果我不是这样一个新手,我只会提出2个请求,因为我不需要唯一的ID保持不变。我现在就知道了。

CREATE TABLE IF NOT EXISTS opinions 
(id INT AUTO_INCREMENT PRIMARY KEY, 
user1 INT, user2 INT, text TEXT);

DELETE FROM opinions WHERE user1='1' AND user2='2';
INSERT INTO opinions (user1, user2, text) VALUES ('1','2','bad guy');

太简单了

cnh2zyt3

cnh2zyt33#

如果不想创建额外的唯一键,也可以使用这个badboy方法。
您知道每个索引都会更慢地更新和删除查询,所以如果您对表进行了大量的写操作,那么在某种程度上可能会比d.smania answer更好。
当记录存在时
查询

REPLACE INTO 
 opinions (
     id
   , user1
   , user2
   , opinion
)

SELECT 
   id
 , user1
 , user2
 , opinion
FROM (

  SELECT
     id
   , user1
   , user2
   , 'bad guy' AS opinion
   , (@found := 1) AS found
  FROM
   opinions
  WHERE
     user1 = 141015727
   AND
     user2 = 627964361

  UNION ALL 

  SELECT
    DISTINCT
       null AS id
     , 141015727 AS user1
     , 627964361 AS user2
     , 'bad guy' AS opinion
     , (@found := 0) AS found  
  FROM
   opinions
  WHERE 
    @found <> 1  
) AS alias
CROSS JOIN (SELECT @found := 0) AS init_var_params

结果

| id  | user1     | user2     | opinion |
| --- | --------- | --------- | ------- |
| 1   | 141015727 | 627964361 | bad guy |
| 2   | 141015727 | 375392538 | hello   |

db fiddle视图
当记录不存在时
查询

REPLACE INTO 
 opinions (
     id
   , user1
   , user2
   , opinion
)

SELECT 
   id
 , user1
 , user2
 , opinion
FROM (

  SELECT
     id
   , user1
   , user2
   , 'bad guy' AS opinion
   , (@found := 1) AS found
  FROM
   opinions
  WHERE
     user1 = 100
   AND
     user2 = 100

  UNION ALL 

  SELECT
    DISTINCT
       null AS id
     , 100 AS user1
     , 100 AS user2
     , 'bad guy' AS opinion
     , (@found := 0) AS found  
  FROM
   opinions
  WHERE 
    @found <> 1  
) AS alias
CROSS JOIN (SELECT @found := 0) AS init_var_params

结果

| id  | user1     | user2     | opinion  |
| --- | --------- | --------- | -------- |
| 1   | 141015727 | 627964361 | nice guy |
| 2   | 141015727 | 375392538 | hello    |
| 3   | 100       | 100       | bad guy  |

db fiddle视图
编辑
我对mysql用户变量进行了一些调整,这在没有mysql用户变量的情况下也是可行的。因此,其他具有replace或其他颠覆方法的数据库系统也可以使用这种方法。
现有的
在db fiddle上没有mysql用户变量视图
不存在
在db fiddle上没有mysql用户变量视图

y53ybaqx

y53ybaqx4#

方案1)
您可以创建一个存储过程来检查是否存在用户\ 1和用户\ 2的值,如果存在重复的值,则执行更新,否则执行插入。我不打算在这里详细介绍,如果您感兴趣,可以阅读有关存储过程的内容。对于您的基本示例,我将在下一个选项中这样做。
方案2)
首先,我们在您拥有的表上创建一个唯一索引:

CREATE TABLE IF NOT EXISTS opinions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user1 INT,
    user2 INT,
    text TEXT,
    CONSTRAINT uniqueUsersTuple UNIQUE (user1, user2)
);

然后,你可以这样做:

INSERT INTO opinions (user1, user2, text)
VALUES (userID1, userID2, "newOpinion")
ON DUPLICATE KEY UPDATE text = "newOpinion";

相关问题