UPDATE SET Increment INNER JOIN, my select works but not update-CodePudding

I have the following SQL command. There are two tables,

table named rbhl_linkednodes looks like this

Id, Node1, Node2
1 , 6 , 7

table named rbhl_nodelist has a bunch of data including id and r, I need to change the r value

Id, R
6, 15
7, 15

The select command works and displays me the above result. When I try to swap it out with update, it does not work.

UPDATE rbhl_nodelist
SET nl.r = nl.r - 3
FROM rbhl_nodelist nl
INNER JOIN rbhl_linkednodes ln
ON ln.node1 = nl.id or ln.node2 = nl.id
WHERE ln.id = 1;

SELECT nl.r
FROM rbhl_nodelist nl
INNER JOIN rbhl_linkednodes ln
ON ln.node1 = nl.id or ln.node2 = nl.id
WHERE ln.id = 1;

The goal is to decrement both values at once by 3 with one command.

CodePudding user response：

Try Not Exists

update rbhl_nodelist
set r = r - 3
from rbhl_nodelist t1
where exists (
select 1 from rbhl_linkednodes t2
where t2.ID = 1
and t2.node1 = t1.id
or t2.node2 = t1.id
)

CodePudding user response：

You almost had it.

Create test case:

CREATE TABLE rbhl_linkednodes (
    id int auto_increment primary key,
    node1 varchar(255),
    node2 varchar(255)
);

CREATE TABLE rbhl_nodelist (
    id int,
    r int
);

insert into rbhl_linkednodes set node1 = 6, node2 = 7;
insert into rbhl_linkednodes set node1 = 16, node2 = 17;
insert into rbhl_linkednodes set node1 = 26, node2 = 27;

insert into rbhl_nodelist set id = 6, r = 15;
insert into rbhl_nodelist set id = 7, r = 15;
insert into rbhl_nodelist set id = 16, r = 15;
insert into rbhl_nodelist set id = 17, r = 15;
insert into rbhl_nodelist set id = 26, r = 15;
insert into rbhl_nodelist set id = 27, r = 15;


select * from rbhl_linkednodes;
 ---- ------- ------- 
| id | node1 | node2 |
 ---- ------- ------- 
|  1 | 6     | 7     |
|  2 | 16    | 17    |
|  3 | 26    | 27    |
 ---- ------- ------- 
select * from rbhl_nodelist;
 ------ ------ 
| id   | r    |
 ------ ------ 
|    6 |   15 |
|    7 |   15 |
|   16 |   15 |
|   17 |   15 |
|   26 |   15 |
|   27 |   15 |
 ------ ------

The update query:

UPDATE rbhl_nodelist nl
INNER JOIN rbhl_linkednodes ln
ON ln.node1 = nl.id or ln.node2 = nl.id
SET nl.r = nl.r - 3
WHERE ln.id = 1;

Results in

select * from rbhl_linkednodes;
 ---- ------- ------- 
| id | node1 | node2 |
 ---- ------- ------- 
|  1 | 6     | 7     |
|  2 | 16    | 17    |
|  3 | 26    | 27    |
 ---- ------- ------- 
select * from rbhl_nodelist;
 ------ ------ 
| id   | r    |
 ------ ------ 
|    6 |   12 | <--- OK
|    7 |   12 | <--- OK
|   16 |   15 |
|   17 |   15 |
|   26 |   15 |
|   27 |   15 |
 ------ ------

So the order is:
update
join on
set where