MySql 仅使用单个选择查询更新表

Question

我使用 table_a 中的数据更新 table_b，如下所示。

table_a

"id"    "addedUser" "addedName"
"1"     "1"         "james"
"2"     "1"         "kirk"
"3"     "1"         "lars"
"4"     "2"         "michael"

update table_b set 
totalFriends = 20, #data I supply
totalRequests = 20, #data I supply
userDisplay = (select addedName from table_a where addedUser = 1 limit 1), 
userFriends = (select group_concat(addedName) from table_a where addedUser = 1 limit 1) 
where 
userId = 1 and userName = 'norman';

table_b

"id"    "userId"    "userName"  "userDisplay"   "userFriends"               "totalFriends"  "totalRequests"
"1"     "1"         "norman"    "james"         "james,kirk,lars"   "20"            "20"

我运行上面的 sql，它给出了我正在寻找的结果。除此之外，我可以通过其他方式执行此操作以避免在其中使用两个 select 语句吗？

实际情况比这复杂得多。我已经对这个问题进行了精简。

Answer 1

您可以做的是编写一个 select 语句，其中包含一些您已经拥有的常量值(对于您提供的数据)。例如，您可以编写以下语句:

SELECT addedName AS userDisplay, GROUP_CONCAT(addedName) AS userFriends, 20 AS totalFriends, 20 AS totalRequests
FROM table_a
WHERE addedUser = 1
LIMIT 1;

然后，您可以在 UPDATE 语句中使用它，如下所示:

UPDATE table_b b
JOIN(
  SELECT addedUser, addedName AS userDisplay, GROUP_CONCAT(addedName) AS userFriends, 20 AS totalFriends, 20 AS totalRequests
  FROM table_a
  WHERE addedUser = 1
  LIMIT 1) temp ON temp.addedUser = b.userid
SET
  b.totalFriends = temp.totalFRiends,
  b.totalRequests = temp.totalRequests,
  b.userDisplay = temp.userDisplay,
  b.userFriends = temp.userFriends
WHERE b.userid = 1 AND b.username = 'Norman';

它对我有用 SQL Fiddle .