0

so i have two tables like this

TableA :

|  id  | name  | src  |
-----------------------
|   1  | T100  | B001 |
|   2  | T100  | B002 |
|   3  | T100  | B003 |
|   4  | T101  | B004 |
|   5  | T101  | B005 |
-----------------------

TableB :

| id | name | flag |
--------------------
| 1  | B001 |   1  |
| 2  | B002 |   1  |
| 3  | B003 |   1  |
| 4  | B004 |   1  |
| 5  | B005 |   1  |
--------------------

i want to update flag in TableB to 0 which used as src on T100 in TableA

TableB that i want after update :

| id | name | flag |
--------------------
| 1  | B001 |   0  |
| 2  | B002 |   0  |
| 3  | B003 |   0  |
| 4  | B004 |   1  |
| 5  | B005 |   1  |
--------------------

here is my query

UPDATE TableB SET flag = 0
FROM TableB b INNER JOIN TableA a 
ON b.name = a.src
WHERE a.name = T100

but the result is, it updates all the flag to 0, not just the T100 sources

TableB query result :

| id | name | flag |
--------------------
| 1  | B001 |   0  |
| 2  | B002 |   0  |
| 3  | B003 |   0  |
| 4  | B004 |   0  |
| 5  | B005 |   0  |
--------------------

is something in my query that i do wrong or is there another way to solve this? Thankyou

Sakura
  • 1
  • 1
  • 2
    [As documented in the manual](https://www.postgresql.org/docs/current/sql-update.html) do **not** repeat the target table in the FROM clause. –  Jul 06 '21 at 10:36
  • `UPDATE TableB SET flag = 0 FROM TableA a WHERE b.name = a.src AND a.name = 'T100';` (or: use exists()) – wildplasser Jul 06 '21 at 10:37
  • https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server?rq=1 – wildplasser Jul 06 '21 at 10:43

0 Answers0