Update based on multiple criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update based on multiple criteria

Author

Topic

vavs
Starting Member

24 Posts

Posted - 2010-09-07 : 17:37:47

I am trying to organize my inventory into a parent child relationship.

I have some inventory that is the original material ( we call it a master coil) From that master coil, we can slit it down to multiple children or babies. Some of that material can go through additional processing. Each time it changes, it will drop down a level.

What I need to do is identify the level of each piece of inventory.
I have two fields that allow me to do this. They are TAG# and OrigTag#. Both are alphanumeric fields. If the TAG# and OrigTag# are the same, this is a master coil. This is not where I need help.

I wrote this code
/*
update INVTEST1
set level = 1
from INVTEST1
where TAG# = OrigTag#
*/

What I need to do now is go to the next level down.

update INVTEST1
set level = 2
from INVTEST1
where OrigTag# = the TAG#'s that are level 1

In other words, I want to match origtag# to the subset of TAG# where they have a level of 1.

If I can do this I can replicate this until I have no tags left. This may be 6 to 7 levels down.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-09-09 : 13:32:02

Could you show some examples from the table?

Because it seems like just based on what you have described this would undo the results of the first UPDATE.


UPDATE INVEST1
SET level = 2
WHERE OrigTag# IN (SELECT TAG# FROM INVEST1 WHERE level = 1)

If you can craft a query that would return only the TAG#s of the items you want to update, just pop it in the IN list and it should work. Make sure you test it. If you need some more help please post some example rows from the table.

===
http://www.ElementalSQL.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-09 : 14:13:53

>> I am trying to organize my inventory into a parent child relationship. <<

Look at the nested sets model for your hierarchy. It will be much easier than what you are trying to do.

Also, the UPDATE.. FROM.. syntax does not work properly and can give you cardinality violations. Use the new MERGE statement instead.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources