CODEWITHSUNDEEP
mysqldatabaserelational-database
Faizal Saleem
Faizal Saleem

Reputation: 19

mysql update if all values not exists in same table else nothing

I have a Table With multiple columns existing data already like

+------+------+--------------+------+---------+
| id   | ref  |name          |role  | status  |
+------+------+--------------+------+---------+
|    1 |   ab |Faizal        |adm   | 1       |
|    2 |   ab |Faizal        |      | 0       |
|    3 |   cd |Faizal        |usr   | 1       |
|    4 |   am |Agrim         |usr   | 1       |
|    5 |   xy |vishal        |usr   | 1       |
|    6 |   xy |vishal        |usr   | 0       |
|    7 |   ad |ankush        |usr   | 1       |
|    8 |   ad |ankush        |      | 0       |
|    9 |   mm |vishal        |oth   | 1       |
+------+------+--------------+------+---------+

and I don't want assign a role to a user multiple time in table,Update should take action only when in the table user haven't the same role with same status. Like I tried that query

UPDATE roles t1
SET
  t1.role   = 'adm',
  t1.status = '1',
  t1.ref    = 'ab'
WHERE t1.id = 2 AND NOT EXISTS
(
    SELECT 1
    FROM roles t2
    WHERE t1.role = t2.role
          AND t1.status = t2.status
          AND t1.ref = t2.ref
)

It Gives an Error I want after executing the query:

+------+------+--------------+------+---------+
| id   | ref  |name          |role  | status  |
+------+------+--------------+------+---------+
|    1 |   ab |Faizal        |adm   | 1       |
|    2 |   ab |Faizal        |      | 0       |
|    3 |   cd |Faizal        |usr   | 1       |
|    4 |   am |Agrim         |usr   | 1       |
|    5 |   xy |vishal        |usr   | 1       |
|    6 |   xy |vishal        |usr   | 0       |
|    7 |   ad |ankush        |usr   | 1       |
|    8 |   ad |ankush        |      | 0       |
|    9 |   mm |vishal        |oth   | 1       |
+------+------+--------------+------+---------+

nothing Changed Because the given key data is already exist in table.

Another Example: Run the query with other key data.

UPDATE roles t1
SET
  t1.role   = 'adm',
  t1.status = '1',
  t1.ref    = 'ad'
WHERE t1.id = 8 AND NOT EXISTS
(
    SELECT 1
    FROM roles t2
    WHERE t1.role = t2.role
          AND t1.status = t2.status
          AND t1.ref = t2.ref
)

after the executing the query table should :

+------+------+--------------+------+---------+
| id   | ref  |name          |role  | status  |
+------+------+--------------+------+---------+
|    1 |   ab |Faizal        |adm   | 1       |
|    2 |   ab |Faizal        |      | 0       |
|    3 |   cd |Faizal        |usr   | 1       |
|    4 |   am |Agrim         |usr   | 1       |
|    5 |   xy |vishal        |usr   | 1       |
|    6 |   xy |vishal        |usr   | 0       |
|    7 |   ad |ankush        |usr   | 1       |
|    8 |   ad |ankush        |      | 0       |
|    9 |   mm |vishal        |oth   | 1       |
+------+------+--------------+------+---------+

Keep in mind Conditions:

  1. We can't INSERT or DELETE in table.
  2. Can't update the rows where status=1

Upvotes: 0

Views: 2163

Answers (1)

trincot
trincot

Reputation: 351369

During the update statement any references to the records in that table still relate to the data as before the actual update, so it is normal that the sub-select does not consider the values you are inserting, but the values which are already there in the table.

Also this syntax with self referencing sub query is not allowed by MySql. Use a left join instead.

So list the literal values you are updating with, in that join condition as follows:

UPDATE    roles t1
LEFT JOIN roles t2 
      ON  t2.role = 'adm' 
      AND t2.status = '1' 
      AND t2.ref = 'ab'
SET
  t1.role   = 'adm',
  t1.status = '1',
  t1.ref    = 'ab'
WHERE t1.id = 2
 AND  t2.id IS NULL

That last condition corresponds to your NOT EXISTS. The LEFT JOIN will produce NULL values in all t2 columns when the JOIN condition yields no results.

If you have a unique key constraint on those three fields, then you can achieve the same with the ignore keyword:

UPDATE IGNORE roles
SET
  role   = 'adm',
  status = '1',
  ref    = 'ab'
WHERE id = 2

If this would result in a duplicate the statement will not do anything, nor report an error.

Upvotes: 2

Related Questions