Help with a Query

[the_grove_man]

Let me paraphrase a query I want to do.

First, I have a table called 'Components'
This table has these fields:

RecNo (Primary key)
FileRec (Foreign key to Files Table)
StandardRec (Foreign key to StandardTypes table)
RefDes (String)
AssemRec (Self-join to a RecNo)

Here is my paraphrase:
UPDATE Components SET AssemRec = the primary key in same table where in the
refdes concatenation '<$-' + Refdes + '>' = RefDes AND the FileRec's are the
same.

So as an example, here is sample data.
RecNo = 5
FileRec = 10
StandardRec = 17
RefDes = <$-156AF>
AssemRec = 0

Since the refdes matches another Refdes in the same table if the brackets,
dollar sign and - are stripped away, I would paste the RecNo into the
AssemRec of the match.

The above data would match this:
RecNo = 10
FileRec = 10 (Same File)
StandardRec = 20
RefDes = 156AF (Notice it matches when stripped of <$->)
AssemRec = 5 (Since the Refdes matches and the filerec matches, I will paste
in the top RecNo into the AssemRec)

Thanks for any help.

 

[Ken Snell \(MVP\)]

Try this:

UPDATE Components SET AssemRec =
(SELECT TOP 1 T.RecNo
FROM Components AS T
WHERE Replace(Replace(T.Refdes,">",""),'<$-',"")
= Replace(Replace(Components.Refdes,">",""),"<$-","")
AND T.FileRec = Components.FileRec AND
T.RecNo Is Not Null
ORDER BY T.RecNo);

Note that you're violating normalization rules when you store multiple data
items in one field -- your Refdes field. It contains the desired value
surrounded by various "delimiters". It would be better, and easier to query
your data, if you stored such information in three separate fields -- one
for the ">", one for the middle value, and one for the ">".

 

[Ken Snell \(MVP\)]

This modified version will not overwrite any records that already have a
value in AssemRec, if that would be your desire:

UPDATE Components SET AssemRec =
(SELECT TOP 1 T.RecNo
FROM Components AS T
WHERE Replace(Replace(T.Refdes,">",""),'<$-',"")
= Replace(Replace(Components.Refdes,">",""),"<$-","")
AND T.FileRec = Components.FileRec AND
T.RecNo Is Not Null
ORDER BY T.RecNo)
WHERE Components.AssemRec Is Null;




And this modified version will not overwrite any records that already have a
nonzero value in AssemRec, if that would be your desire:

UPDATE Components SET AssemRec =
(SELECT TOP 1 T.RecNo
FROM Components AS T
WHERE Replace(Replace(T.Refdes,">",""),'<$-',"")
= Replace(Replace(Components.Refdes,">",""),"<$-","")
AND T.FileRec = Components.FileRec AND
T.RecNo Is Not Null
ORDER BY T.RecNo)
WHERE Components.AssemRec Is Null
OR Components.AssemRec = 0;

 

[the_grove_man]

Thanks,
I finally got it..

UPDATE Components AS A
INNER JOIN Components AS B
ON (A.FileRec = B.FileRec) AND
(mid(A.RefDes,4, len(A.refdes)-4) = B.RefDes)
SET B.AssemRec = A.RecNo
WHERE A.StandardRec=17;