Link to home
Start Free TrialLog in
Avatar of maverick0728
maverick0728Flag for United States of America

asked on

SQL Query to update table with prior field value

I have a table "estimate".  It contains the fields:

I need to update the "priorpeer" column.  The query needs to use the "prefix" column to determine the update.  The rules are:
if prefix = 0 then "priorpeer" = -1
if itemno ends in .0 then "priorpeer" = -1
if the first instance of the prefix (when the query is sorted by itemno) then "priorpeer" = -1
otherwise, while still sorted by itemno, the "priorpeer" = assemblyseq of the prior record with the matching prefix.  

See the attached excel spreadsheet for the desired update to "priorpeer" field.  column "H" shows what I want the end update result to be.

Thanks for your help.
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

A4 doesn't end in 0 it ends in 1 A4 would have to be 1.0 the same holds for all values that you want to end in .0
Avatar of maverick0728


A4 actually does end in .0.  I see they got dropped when I pasted the data into Excel.  See updated spreadsheet.
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can create a table (temp or view) with those values using the following select:
select itemno,'0' as prevno, assembly_seq, prefix, 0 as prior_seq, '0' as prevfix, -1 as priorpeer
from estimate where prefix = '0'
union all
select x.itemno, y.itemno, x.assembly_seq, x.prefix, y.assembly_seq as prior_seq, y.prefix as prevfix,
case when length(y.prefix) = 1 then -1
     when x.prefix > y.prefix then -1
     when x.prefix = y.prefix then y.assembly_seq
     else -1
end priorpeer--, x.rn xrn, y.rn yrn
(select itemno, assembly_seq, prefix, priorpeer,
 row_number() over (order by prefix, assembly_seq) rn
 from estimate) x,
(select itemno, assembly_seq, prefix, priorpeer,
 row_number() over (order by prefix, assembly_seq) rn
 from estimate) y
where x.rn = y.rn + 1

You can then use merge or a join for your update.
I used the LAG and LEAD functions, since this will be hosted on SQL 2012.  Thanks