SQL Updating a single table comparing information from two tables

Question

I have the following problem:

Suppose I have defined TWO tables

USERS

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

USERSADD

 ID (int. key) TYPE (String) 

The second table stores additional information for USERS. Obviously, real tables are more complicated, but this is an idea. (Do not ask me why another table is created instead of adding fields to the first table, this is my boss idea).

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

 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 a comma between two tables ( UPDATE USERS U , USERSADD A ). Hope I was clear enough ...

Will anyone kindly provide me a solution? Thanks in advance.

Answer 1

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

Answer 2

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

Answer 3

The syntax you use uses the implicit INNER JOIN. It would be better 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 

Answer 4

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