CODEWITHSUNDEEP
mysqlsql
Jecki
Jecki

Reputation: 802

UPDATE IF EXISTS error

Im try to update value (level) in in table people if userid exist in users table in table

  IF EXISTS (SELECT userid FROM users)
  UPDATE people SET level='1'

What i am missing ! to get errors ?

Upvotes: 3

Views: 15059

Answers (3)

Denys Séguret
Denys Séguret

Reputation: 382170

From your comment "i need to update level column if userid exist in both table with value 1", it seems you need this :

UPDATE
    `people`
SET
    `level` = 1
WHERE
    EXISTS (
        SELECT
            *
        FROM
            `users`
        WHERE
            users.userid = people.userid
    );

Upvotes: 7

Salvatore NashCrash De Bonis
Salvatore NashCrash De Bonis

Reputation: 21

With MySQL, "EXISTS" clause on "UPDATE" simply don't work.

Use instead a JOIN condiction:

UPDATE people A, (SELECT userid FROM users) B SET A.level='1'

However, I suppose you should use some WHERE clause also... ;)

For example, this one update "level" on "people" table, only if exists a join value on "users" table...

UPDATE people A, users B SET A.level='1' WHERE A.userid=B.userid;

Upvotes: 2

Eugeny89
Eugeny89

Reputation: 3731

You don't need to do select. All you need is:

UPDATE people SET level='1' WHERE userid IS NOT NULL

BTW, what db are you running?

UPD. Sorry, I missed the fact that you have two tables. I can recomend to do something like the following:

UPDATE people SET level='1' WHERE EXISTS (SELECT * FROM users WHERE 
users.userid = people.userid)

Also, UPDATE can be done with JOIN, but that depends on what db are you using.

Upvotes: 3

Related Questions