使用连接表进行 sql 更新

发布于 2025-01-03 19:35:24 字数 1261 浏览 3 评论 0原文

假设我有这个 select 语句：

SELECT ut.user_id,
       a.firstname,
       a.surname,
       u.username,
       u.email_address
  FROM administrators a 
  JOIN user_types ut JOIN users u ON a.admin_id = ut.type_id 
   AND u.user_id = ut.user_id AND ut.user_type = 'ADMIN';

假设我想更新 user_id = 1 行中的所有值；

我可以单独更新每个表，或者我可以创建一个像这样的视图：

CREATE OR REPLACE VIEW full_admin AS
SELECT ut.user_id,
       a.firstname,
       a.surname,
       u.username,
       u.email_address
  FROM administrators a 
  JOIN user_types ut 
  JOIN users u  on a.admin_id = ut.type_id 
   AND u.user_id = ut.user_id 
   AND ut.user_type = 'ADMIN'

然后我可以写：

UPDATE full_admin
   SET firstname = 'blah', etc, etc
 WHERE user_id = 1;

这将更新所有表中的所有行

有没有办法在不创建视图的情况下执行此操作？

类似的东西：

UPDATE (
    SELECT ut.user_id,
           a.firstname,
           a.surname,
           u.username,
           u.email_address
      FROM administrators a 
      JOIN user_types ut 
      JOIN users u ON a.admin_id = ut.type_id 
       AND u.user_id = ut.user_id 
       AND ut.user_type = 'ADMIN'
)
AS temp_table
  SET firstname = "ALEX"
WHERE user_id = 1;

但这不起作用

原文

Say I have this select statement:

SELECT ut.user_id,
       a.firstname,
       a.surname,
       u.username,
       u.email_address
  FROM administrators a 
  JOIN user_types ut JOIN users u ON a.admin_id = ut.type_id 
   AND u.user_id = ut.user_id AND ut.user_type = 'ADMIN';

Say I wanted to update ALL values in the row where user_id = 1;

I could update EACH TABLE individually OR i could create a view like so:

CREATE OR REPLACE VIEW full_admin AS
SELECT ut.user_id,
       a.firstname,
       a.surname,
       u.username,
       u.email_address
  FROM administrators a 
  JOIN user_types ut 
  JOIN users u  on a.admin_id = ut.type_id 
   AND u.user_id = ut.user_id 
   AND ut.user_type = 'ADMIN'

Then I could write:

UPDATE full_admin
   SET firstname = 'blah', etc, etc
 WHERE user_id = 1;

And that will update all rows in all tables

Is there a way to do this WITHOUT creating a view?

Something like:

UPDATE (
    SELECT ut.user_id,
           a.firstname,
           a.surname,
           u.username,
           u.email_address
      FROM administrators a 
      JOIN user_types ut 
      JOIN users u ON a.admin_id = ut.type_id 
       AND u.user_id = ut.user_id 
       AND ut.user_type = 'ADMIN'
)
AS temp_table
  SET firstname = "ALEX"
WHERE user_id = 1;

However that doesnt work

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

救星 2025-01-10 19:35:24

如果您想同时更新管理员和用户中的列，那么类似这样的事情应该可以解决问题：

UPDATE administrators a  
        INNER JOIN user_types ut ON a.admin_id = ut.type_id 
        INNER JOIN users u ON u.user_id = ut.user_id 
            SET u.firstname = "ALEX", a.SomeColumn = 'X' 
        WHERE u.user_id = 1 
                 AND ut.user_type = 'ADMIN'

SET 语句部分可以作用于连接中的任何列。

（如果这就是你所问的！）

If you want to update columns in both administrators and users at once, then something like this should do the trick:

UPDATE administrators a  
        INNER JOIN user_types ut ON a.admin_id = ut.type_id 
        INNER JOIN users u ON u.user_id = ut.user_id 
            SET u.firstname = "ALEX", a.SomeColumn = 'X' 
        WHERE u.user_id = 1 
                 AND ut.user_type = 'ADMIN'

The SET statement section can work on any of the columns in the join.

(If that's what you are asking!)

回复收藏 0 原文

娜些时光，永不杰束 2025-01-10 19:35:24

UPDATE administrators a 
INNER JOIN user_types ut ON a.admin_id = ut.type_id
INNER JOIN users u ON u.user_id = ut.user_id AND ut.user_type = 'ADMIN'
SET u.firstname = "ALEX"
WHERE u.user_id = 1
;

当然，只有当连接的表结构可更新时，这才有效（同样的情况也适用于视图）。

UPDATE administrators a 
INNER JOIN user_types ut ON a.admin_id = ut.type_id
INNER JOIN users u ON u.user_id = ut.user_id AND ut.user_type = 'ADMIN'
SET u.firstname = "ALEX"
WHERE u.user_id = 1
;

Ofcourse this will only work (same thing applies to the view) if the joined table construct is updateable.

回复收藏 0 原文