UPDATE question - SQL Server Forums

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 question

Author

Topic

frank2
Starting Member

35 Posts

Posted - 2005-08-18 : 15:32:42

I am trying to learn to work with a recursive table. I found this piece of code (I lost the link to where I got it. If it is familiar to anyone please send me the URL.) I think I was understanding it up to the “UPDATE p” line. Is “P” am alias for BillOfMaterial? How does that work?

--Since the PK of the part is being used to construct the Lineage, you must Specify the maximum length to which the PK can be “CAST” when the PK is converted from an int to varchar. Because we are indexing on a varchar the strings must be the same length. So the new cast value must be padded to assure a consistant length.
declare @MaxPadLength char(8)
set @MaxPadLength = '00000000'

--The display length of the padded part PK.
declare @DisplayPadLength TinyInt
set @DisplayPadLength = 2

UPDATE BillOfMaterial
set depth=0, lineage=NULL

UPDATE BillOfMaterial
set depth=1, lineage=right(@MaxPadLength + CAST(BillOfMaterial.FK_CorpPartMaster_NextAssembly AS varchar(255)),@DisplayPadLength)
where FK_CorpPartsMaster_Part = FK_CorpPartMaster_NextAssembly

WHILE EXISTS
(
SELECT *
FROM BillOfMaterial
WHERE Depth=0
)

UPDATE P
set P.depth=B.Depth+1,
P.Lineage=B.Lineage
+'\'+right(@MaxPadLength+ CAST(P.FK_CorpPartsMaster_Part AS varchar(255)),@DisplayPadLength)

FROM BillOfMaterial AS P
INNER JOIN BillOfMaterial AS B
ON (P.FK_CorpPartMaster_NextAssembly=B.FK_CorpPartsMaster_Part) WHERE B.Depth>0 AND B.Lineage Is Not Null AND P.Depth=0

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 15:54:16

I favour columns (possily in a separate one:one table, rather than polluting the table itself with "tree" control data) that provide:

o PATH - full "path" from the Root node (e.g. listing the ID numbers of all the parents)

o SEQUENCE NUMBER - a number which can be used to ORDER BY any selection and will provide the results in "tree walking-order"

We tend to use Sequence Numbers that have "reasonable gaps for insertions" so that we only renumber the whole tree in relatively rare circumstances.

Kristen

frank2
Starting Member

35 Posts

Posted - 2005-08-18 : 16:18:27

Thanks for the input. But my fundimental question is in the line of code UPDATE P is P an alias for BillOfMaterial? If so the the alias decleration happening in the FROM statement?

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 02:56:48

Sorry, I didn't read the question properly ...

Yes, you are correct. You can either do

UPDATE MyTable
SET MyColumn = 'foo'
WHERE OtherColumn = 'bar'

or

UPDATE MyAlias
SET MyColumn = 'foo'
FROM MyTable AS MyAlias
JOIN OtherTable AS OtherAlias
ON OtherAlias.MyColumn = MyAlias.MyColumn
WHERE OtherColumn = 'bar'

(The AS is optional)

Personally I favour the second style, even if there is only one table, for two reasons

1) Its easy to add a second table without ambiguity (yes, also possible with the first method, but I always use the Alias "U" for the table being updated, therefore any of our code is easily, and safely, readable later on)

2) Its easier to test - just change the syntax to:

UPDATE U
SET MyColumn = 'foo'
-- SELECT *
FROM MyTable AS U
WHERE OtherColumn = 'bar'

and highlight & exec from the red SELECT bit to the end of the statement to see which records will be effected by the UPDATE

Kristen

frank2
Starting Member

35 Posts

Posted - 2005-08-23 : 10:31:28

Thanks, that answeres it for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources