I have two tables: micros.dev_def and micros.lan_node_def
The important columns and values are:
micros.dev_def -
obj_num = 100
lan_node_seq = ? <-- lets say 1 for now. not important
micros.lan_node_def
lan_node_seq = 2 <-- could be any number, but important
ip_addr = 192.168.100.100
What I'm trying to do is have an update statement to update the micros.dev_def lan_node_seq to whatever the micros.lan_node_def lan_node_seq number is. The problem with it is that at any given store this is supposed to be run at, I have no idea what the lan_node_def's lan_node_seq is. At any given store, I know that the one I want will always be ip_addr = 192.168.100.100 (back office server ip)
What I tried was
Code:
UPDATE micros.dev_def
SET d.lan_node_seq = l.lan_node_seq
FROM micros.dev_def d JOIN micros.lan_node_def l
WHERE d.obj_num = 100;
Which failed because I didn't specify the ip address
and
Code:
UPDATE micros.dev_def
SET d.lan_node_seq = l.lan_node_seq
FROM micros.dev_def d JOIN micros.lan_node_def l
WHERE d.obj_num = 100 and l.ip_addr = '192.168.100.100';
Which failed for some reason
I'm not really sure if the following would even work, because I have no idea how to format it to SQL's liking
Code:
UPDATE micros.dev_def
SET lan_node_seq = (FROM micros.lan_node_def WHERE ip_addr = '192.168.100.100')
WHERE obj_num = 100;
Any thoughts out there? I can do simple updates and insert, but when it comes to cross referencing tables I get horribly lost.