*EDIT* ugh... nevermind. I think this is Oracle only
*end edit*
No FROM.. ugh.
Would an IF - THEN statement work? something like (warning, horrible syntax coming. feel free to correct me!)
Code:
IF ip_addr = 192.168.100.100 AND lan_node_seq = 1
FROM micros.lan_node_def THEN
UPDATE micros.dev_def
SET lan_node_seq = 1
WHERE obj_num = 100;
ELSIF ip_addr = 192.168.100.100 AND lan_node_seq = 2
FROM micros.lan_node_def THEN
UPDATE micros.dev_def
SET lan_node_seq = 2
WHERE obj_num = 100;
ELSIF ip_addr = 192.168.100.100 AND lan_node_seq = 3
FROM micros.lan_node_def THEN
UPDATE micros.dev_def
SET lan_node_seq = 3
WHERE obj_num = 100;
ELSIF ip_addr = 192.168.100.100 AND lan_node_seq = 4
FROM micros.lan_node_def THEN
UPDATE micros.dev_def
SET lan_node_seq = 4
WHERE obj_num = 100;
ELSE ip_addr = 192.168.100.100 AND lan_node_seq = 5
FROM micros.lan_node_def THEN
UPDATE micros.dev_def
SET lan_node_seq = 5
WHERE obj_num = 100;
END IF;
or this? Unless I can't create ugly update statements with two tables
Code:
UPDATE micros.dev_def d, micros.lan_node_sys l
SET d.lan_node_sys = 1
WHERE l.ip_addr = '192.168.100.100' AND l.ip_addr = 1;
UPDATE micros.dev_def d, micros.lan_node_sys l
SET d.lan_node_sys = 2
WHERE l.ip_addr = '192.168.100.100' AND l.ip_addr = 2;
UPDATE micros.dev_def d, micros.lan_node_sys l
SET d.lan_node_sys = 3
WHERE l.ip_addr = '192.168.100.100' AND l.ip_addr = 3;
UPDATE micros.dev_def d, micros.lan_node_sys l
SET d.lan_node_sys = 4
WHERE l.ip_addr = '192.168.100.100' AND l.ip_addr = 4;
UPDATE micros.dev_def d, micros.lan_node_sys l
SET d.lan_node_sys = 5
WHERE l.ip_addr = '192.168.100.100' AND l.ip_addr = 5;
UPDATE micros.dev_def d, micros.lan_node_sys l
SET d.lan_node_sys = 6
WHERE l.ip_addr = '192.168.100.100' AND l.ip_addr = 6;