时间戳

首页 > 解决方案 > 更新链接表

mysql - 更新链接表

问题描述

我正在验证社区中的 SQL 方法，我正在完全更新用户的角色。（连接表的完整更新）

用户

+----+-------+------+
| id | first | last |
+----+-------+------+
|  1 | John  | Doe  |
|  2 | Jane  | Doe  |
+----+-------+------+

角色

+----+----------+
| id |   name   |
+----+----------+
|  1 | admin    |
|  2 | accounts |
|  3 | sales    |
+----+----------+

用户角色

+--------+--------+
| userid | roleid |
+--------+--------+
|      1 |      1 |
|      1 |      2 |
|      1 |      3 |
|      2 |      2 |
|      2 |      3 |
+--------+--------+

我的 SQL 方法 -> 首先删除所有，然后插入所有记录

DELETE FROM UserRole WHERE userid = 1;
INSERT INTO UserRole(userid, roleid) VALUES(1, 2), (1, 3);

有没有更好的办法？我的意思是可能在单个查询中为这些类型的链接/连接表执行此操作？

编辑

我想我应该说的是找到一个高效的 SQL 操作而不是单个查询。

这是另一个 SQL

DELETE FROM UserRole WHERE user_id = 1 AND role_id NOT IN (2, 3);
INSERT INTO UserRole(user_id, role_id) VALUES(1, 2), (1, 3)
ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), role_id = VALUES(role_id);

标签： mysqlsql

解决方案

如果要将所有用户的所有角色添加到 userRoles 表中，可以从 userRoles 中删除，然后重新创建表，如下所示：

DECLARE @users TABLE ( userID INT, UserName NVARCHAR(MAX) )
DECLARE @roles TABLE ( roleID INT, RoleName NVARCHAR(MAX) )
DECLARE @userRoles TABLE ( userID INT, roleID INT )

INSERT INTO @users (userID,UserName) VALUES (1,'name1'),(2,'name2'),(3,'name3')
INSERT INTO @roles (roleID,RoleName) VALUES (1,'admin'),(2,'accounts'),(3,'sales')

INSERT INTO @userRoles (userID,roleID)
SELECT U.userID,R.roleID FROM @users U
FULL OUTER JOIN @roles R ON 1=1
ORDER BY userID

SELECT * FROM @userRoles

输出：

userID  roleID
1   1
2   1
3   1
1   2
2   2
3   2
1   3
2   3
3   3

推荐阅读