CODEWITHSUNDEEP
sqlsql-updatederby
Eddie
Eddie

Reputation: 41

SQL Update one table comparing info from two tables

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.

Upvotes: 4

Views: 10304

Answers (4)

shivam
shivam

Reputation: 1

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

Upvotes: 0

Philip Sheard
Philip Sheard

Reputation: 5825

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

Upvotes: 4

squillman
squillman

Reputation: 13641

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

Upvotes: 1

THEn
THEn

Reputation: 1938

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

Upvotes: 3

Related Questions