[SQL] Another table lookup? or something... - Tilted Forum Project Discussion Community

meanSpleen · 09-09-2006, 04:57 PM

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.

xepherys · 09-10-2006, 08:05 PM

What SQL are you using? MS-SQL? MySQL? PostgreSQL?

Really, you only need one call in the WHERE statement, so it should not fail becuase there is no IP. The WHERE clause doesn't even need to be unique, as it will just make those updates to all lines where the clause is true.

Honestly I don't udnerstand exactly what you want to do. What I understand is:

lan_node_def.lan_node_seq is a unique key (assumption)? And there is an IP address associated with it, but it's ALWAYS the same address? Perhaps I'm wrong, that's just how it sounded. Also, is micros the db name or a table name? It seems that micros is the db, lan_node_def and dev_def are tables and everything else is a column?

At any rate, with some more info, I believe I can help you.

meanSpleen · 09-10-2006, 11:34 PM

ack!

I usually access the data through Sybase, but I'm not really sure what type of SQL it is. I'm pretty sure its not MySQL. Micros is a POS system for the restaurant chain I work at, 'Micros Database' would be the correct term for it then

Let's see if I can give a better example though:
Line 1 - (database name).(table)
Line 2 - Column Names
Line 3+ - Data

Code:

micros.dev_def
obj_num     lan_node_seq
1           3
2           4
3           5
4           6
6           9
100      ?

Code:

micros.lan_node_def
lan_node_seq     ip_addr
2           192.168.100.100
3           192.168.100.101
4           192.168.100.102
5           192.168.100.103
6           192.168.100.104
9           192.168.100.106

So the idea is that I want the lan_node_seq in the micros.dev_def table to have its lan_node_seq to be equal to whatever the lan_node_seq is that has the ip_addr equal to 192.168.100.100

In the above example (in terms of logic, not a sql statement):
Where obj_num in micros.def_dev is equal to 100, then set lan_node_seq to the lan_node_seq in micros.lan_node_def where the ip_addr is equal to 192.168.100.100

result - set obj_num equal to 2

Now, I could set this up by hand, but there is a pile of stores this is going to be going into, and I just don't have the time to do it. I just want the update script to look it up.

Example:
In a different store, they have:

Code:

micros.lan_node_def
lan_node_seq     ip_addr
6           192.168.100.100

So at that store, the script would look up that table and say, "Oh, where the ip_addr is equal to 192.168.100.100, the lan_node_seq is equal to 6. I will use this information now to fill in the lan_node_seq to be equal to 6 in the micros.dev_def table now"

Thanks for the help!

captobvious · 09-10-2006, 11:45 PM

Quote:

Originally Posted by meanSpleen

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.

I haven't worked with Sybase before, so I'm not exactly sure what type of SQL statements you can use. However, I'm pretty sure an UPDATE statement can't include a FROM clause regardless of what database system you're using, so I think your SQL statements are failing because of a syntax error.

meanSpleen · 09-11-2006, 04:17 PM

*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;

robot_parade · 09-11-2006, 05:55 PM

Ok. I'm not *entirely* sure what you're asking for. Let me restate your request, just to make sure.

You want to update a record in micros.dev_def where the obj_num = 100. Just to make sure, this is the *only* field you are selecting against - in other words, and record with obj_num = 100 will be updated in the same way, and no other records will be updated.

You want to update this table so that the lan_node_seq is equal to the lan_node_seq from micros.lan_node_def where ip_addr = 192.168.100.100

If the above description is correct, *and* there is a unique constraing on micros.lan_node_def.ip_addr, *then* the following should work:
<code>
UPDATE micros.dev_def
SET lan_node_seq = (SELECT lan_node_seq FROM micros.lan_node_def WHERE ip_addr = '192.168.100.100')
WHERE obj_num = 100
</code>

That should work, barring typos. However, if the SELECT part of the query returns more than one row, or no rows, you are screwed. :-)

Another (and probably better) way to do it is programatically, where you would run the 'SELECT' part of the query, and see what you get.

If you get exactly one, then run an UPDATE query, replacing the (SELECT ...) part with a bind var (?), and you're done.

If you get 0 or more than one, the application should return an error of some sort.

This is the way I would do it, unless I was absolutely certain that I would get back exactly one result from the SELECT.

meanSpleen · 09-11-2006, 07:49 PM

Correct on obj_num = 100, and 100 only.

You know, that Update almost looks too simple now that I see it. I will definitely test it out on one of my database copies and let you know how it works out for me. Thanks!

- the select part 'should' work with a return of only one row. There should only be one ip_addr = '192.168.100.100' in this stupid thing...

*okay maybe tomorrow (weds). One of the stores blew up today, and I spent the entire time rebuilding

*** Worked fantastically! Verified the select statement first to ensure just one row was being returned. Stuck it into my update script and it went through without any problems. Thanks much for the help!

Nimetic · 09-18-2006, 04:58 AM

Yup. I think that should be

(SELECT ... FROM )

Otherwise - how will the DB know what column to use?

09-09-2006, 04:57 PM	#1 (permalink)
meanSpleen Squid hat! Location: A Few Miles Away From Halx	[SQL] Another table lookup? or something... 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. __________________ Like TFP? Donate To Keep It Alive!!

09-10-2006, 08:05 PM	#2 (permalink)
xepherys <3 TFP Location: 17TLH2445607250	What SQL are you using? MS-SQL? MySQL? PostgreSQL? Really, you only need one call in the WHERE statement, so it should not fail becuase there is no IP. The WHERE clause doesn't even need to be unique, as it will just make those updates to all lines where the clause is true. Honestly I don't udnerstand exactly what you want to do. What I understand is: lan_node_def.lan_node_seq is a unique key (assumption)? And there is an IP address associated with it, but it's ALWAYS the same address? Perhaps I'm wrong, that's just how it sounded. Also, is micros the db name or a table name? It seems that micros is the db, lan_node_def and dev_def are tables and everything else is a column? At any rate, with some more info, I believe I can help you. __________________ The prospect of achieving a peace agreement with the extremist group of MILF is almost impossible... -- Emmanuel Pinol, Governor of Cotobato My Homepage

09-10-2006, 11:34 PM	#3 (permalink)
meanSpleen Squid hat! Location: A Few Miles Away From Halx	ack! I usually access the data through Sybase, but I'm not really sure what type of SQL it is. I'm pretty sure its not MySQL. Micros is a POS system for the restaurant chain I work at, 'Micros Database' would be the correct term for it then Let's see if I can give a better example though: Line 1 - (database name).(table) Line 2 - Column Names Line 3+ - Data Code: micros.dev_def obj_num lan_node_seq 1 3 2 4 3 5 4 6 6 9 100 ? Code: micros.lan_node_def lan_node_seq ip_addr 2 192.168.100.100 3 192.168.100.101 4 192.168.100.102 5 192.168.100.103 6 192.168.100.104 9 192.168.100.106 So the idea is that I want the lan_node_seq in the micros.dev_def table to have its lan_node_seq to be equal to whatever the lan_node_seq is that has the ip_addr equal to 192.168.100.100 In the above example (in terms of logic, not a sql statement): Where obj_num in micros.def_dev is equal to 100, then set lan_node_seq to the lan_node_seq in micros.lan_node_def where the ip_addr is equal to 192.168.100.100 result - set obj_num equal to 2 Now, I could set this up by hand, but there is a pile of stores this is going to be going into, and I just don't have the time to do it. I just want the update script to look it up. Example: In a different store, they have: Code: micros.lan_node_def lan_node_seq ip_addr 6 192.168.100.100 So at that store, the script would look up that table and say, "Oh, where the ip_addr is equal to 192.168.100.100, the lan_node_seq is equal to 6. I will use this information now to fill in the lan_node_seq to be equal to 6 in the micros.dev_def table now" Thanks for the help! __________________ Like TFP? Donate To Keep It Alive!!

09-11-2006, 07:49 PM	#7 (permalink)
meanSpleen Squid hat! Location: A Few Miles Away From Halx	Correct on obj_num = 100, and 100 only. You know, that Update almost looks too simple now that I see it. I will definitely test it out on one of my database copies and let you know how it works out for me. Thanks! - the select part 'should' work with a return of only one row. There should only be one ip_addr = '192.168.100.100' in this stupid thing... okay maybe tomorrow (weds). One of the stores blew up today, and I spent the entire time rebuilding ** Worked fantastically! Verified the select statement first to ensure just one row was being returned. Stuck it into my update script and it went through without any problems. Thanks much for the help! __________________ Like TFP? Donate To Keep It Alive!! Last edited by meanSpleen; 09-13-2006 at 07:31 PM..

09-11-2006, 05:55 PM	#6 (permalink)
robot_parade Junkie Location: San Antonio, TX	Ok. I'm not entirely sure what you're asking for. Let me restate your request, just to make sure. You want to update a record in micros.dev_def where the obj_num = 100. Just to make sure, this is the only field you are selecting against - in other words, and record with obj_num = 100 will be updated in the same way, and no other records will be updated. You want to update this table so that the lan_node_seq is equal to the lan_node_seq from micros.lan_node_def where ip_addr = 192.168.100.100 If the above description is correct, and there is a unique constraing on micros.lan_node_def.ip_addr, then the following should work: <code> UPDATE micros.dev_def SET lan_node_seq = (SELECT lan_node_seq FROM micros.lan_node_def WHERE ip_addr = '192.168.100.100') WHERE obj_num = 100 </code> That should work, barring typos. However, if the SELECT part of the query returns more than one row, or no rows, you are screwed. :-) Another (and probably better) way to do it is programatically, where you would run the 'SELECT' part of the query, and see what you get. If you get exactly one, then run an UPDATE query, replacing the (SELECT ...) part with a bind var (?), and you're done. If you get 0 or more than one, the application should return an error of some sort. This is the way I would do it, unless I was absolutely certain that I would get back exactly one result from the SELECT.

09-18-2006, 04:58 AM	#8 (permalink)
Nimetic Junkie Location: Melbourne, Australia	Yup. I think that should be (SELECT ... FROM ) Otherwise - how will the DB know what column to use?