SQL Query to update table with prior field value

I have a table "estimate".  It contains the fields:
itemno
assemblyseq
prefix
parentnum
partnum
priorpeer

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.
ExamplePriorPeer.xlsx
maverick0728Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
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
maverick0728Author Commented:
A4 actually does end in .0.  I see they got dropped when I pasted the data into Excel.  See updated spreadsheet.
ExamplePriorPeer-updated.xlsx
Kent OlsenDBACommented:
Hi Maverick,

One of the more irritating things about SQL is that Micro$oft, IBM, Oracle, et al, all have different syntax to achieve the same result.  I don't have a SQL Server instance at my disposal unless I'm at the office so the best I can do right now is outline the solution.

If we can identify the new value, the actual update is nearly trivial.  So...

With SQL Server 2013 the LAG and LEAD functions will fetch items from the adjacent rows.  Since you tagged 2008, a CTE should work fine.  (It'll work with 2013, too.)

WITH t
AS
(
  SELECT row_number () rn, t0.* FROM mytable
  ORDER BY itemno
),
tt
AS
(
  SELECT t0.*, t1.itemno previousitem
  FROM t t0
  LEFT JOIN t t1
  ON t0.rn = t1.rn-1
)

Open in new window


Now you can apply your logic to the rows returned by tt

SELECT tt.* , 
  case when previousitem is NULL then -1
       when ...
       else end
from tt

Open in new window



Then it's a simply matter to change the SELECT into an UPDATE statement.

Kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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
from
(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.
maverick0728Author Commented:
I used the LAG and LEAD functions, since this will be hosted on SQL 2012.  Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.