SQL 更新一张表，比较两张表的信息

发布于 2024-10-27 10:46:42 字数 635 浏览 4 评论 0原文

我有以下问题：

假设我定义了两个表

USERS

ID (int. key)  
NAME (String)  
SALARY (currency)

USERSADD

ID (int. key)  
TYPE (String)

第二个表存储 USERS 的附加信息。显然真实的表格更复杂，但这就是想法。（不要问我为什么要创建另一个表而不是向第一个表添加字段，这是我老板的想法）。

现在，如果满足第二个表的条件，我尝试更新第一个表。
像这样的事情：

UPDATE USERS U, USERSADD A  
SET U.SALARY = 1000  
WHERE U.ID = A.ID  
AND A.TYPE = 'Manager'

在 Netbeans Derby 中，我有一个错误：“，在 X 列中找到”，它指的是两个表之间的逗号 (UPDATE USERS U, USSADD A）。我希望我说得足够清楚......

有人愿意为我提供解决方案吗？提前致谢。

原文

I have the following problem:

Let's suppose I defined TWO tables

USERS

ID (int. key)  
NAME (String)  
SALARY (currency)

USERSADD

ID (int. key)  
TYPE (String)

The 2nd table stores additional information for USERS. Obviously the real tables are more complicated but this is the idea. (Don't ask me why another table is created instead of adding fields to the first table, this is my boss's idea).

Now I am trying to UPDATE the first table if a condition from second table is satisfied.
Something like this:

UPDATE USERS U, USERSADD A  
SET U.SALARY = 1000  
WHERE U.ID = A.ID  
AND A.TYPE = 'Manager'

In Netbeans Derby I have an error: ", found in column X", and it refers to the comma between the two tables (UPDATE USERS U, USERSADD A). I hope I was clear enough...

Would somebody be kind enough to provide me with a solution? Thanks in advance.

分享到QQ

分享到微博

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

发布评论

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

绅士风度i 2024-11-03 10:46:42

UPDATE USERS
SET SALARY = 1000
WHERE ID IN (
SELECT ID FROM USERSADD 
WHERE TYPE = 'Manager')

UPDATE USERS
SET SALARY = 1000
WHERE ID IN (
SELECT ID FROM USERSADD 
WHERE TYPE = 'Manager')

回复收藏 0 原文

时常饿 2024-11-03 10:46:42

UPDATE USERS 
       SET USERS.SALARY = 1000  
  FROM USERS JOIN USERSADD ON USERS.ID = USERSADD.ID 
 WHERE USERSADD.TYPE ='MANAGER'

UPDATE USERS 
       SET USERS.SALARY = 1000  
  FROM USERS JOIN USERSADD ON USERS.ID = USERSADD.ID 
 WHERE USERSADD.TYPE ='MANAGER'

回复收藏 0 原文

过气美图社 2024-11-03 10:46:42

您使用的语法使用隐式 INNER JOIN。最好使用显式联接。尝试这样的事情：

UPDATE Users
SET Salary = 1000
FROM Users u
INNER JOIN Usersadd a on u.id=a.id
    AND a.Type = 'Manager

The syntax you are using uses an implicit INNER JOIN. It would be better for you to use an explicit join. Try something like this:

UPDATE Users
SET Salary = 1000
FROM Users u
INNER JOIN Usersadd a on u.id=a.id
    AND a.Type = 'Manager

回复收藏 0 原文

淡淡離愁欲言轉身 2024-11-03 10:46:42

UPDATE USERSU
SET SALARY = 1000
WHERE exist IN (
        SELECT ID
        FROM USERSADD A
        WHERE TYPE = 'Manager'
            AND U.id = A.id
    )

UPDATE USERSU
SET SALARY = 1000
WHERE exist IN (
        SELECT ID
        FROM USERSADD A
        WHERE TYPE = 'Manager'
            AND U.id = A.id
    )

回复收藏 0 原文

~没有更多了~