Hi All,

We are converting our MRP system to point to SQL 2005 and I have some questions for all the GURUs out there..

•Table Name – JOMast
•If last 2 digit of JobNo ‘00’ means Main job
•If last 2 digit of JobNo NOT ‘00’ means Sub job
•Need to update the SONO for ALL the jobs in table below according to Main Job QtNo

JobNo QtNo SONo
I05-00 1234
I05-01
I05-02
I05-03
I06-00 5678
I06-01
I06-02
I06-03
I06-04

*-----------------------

This is how I did in VFP

Select JobMast && Sorted according to JobNo

Locate

Scan for Not EOF()

lcJobno = JObno

*-- Get the value from main job and plug into sub jobs
Do Case
Case SubString(lcJobno,5,2) = ‘00’ && Main Job
m.SoNo = QtNo
Case SubString(lcJobno,5,2) <> ‘00’ && Sub Job
Replace Sono with m.SoNo
Endcase

Endscan

*-----------------------

Here comes my question..

How do I use SQL 2005 Update command to replace the value in SONo column Can I use the ‘Replace ‘ ? If so how ?

I tried the following in SSMS but that will update only the main jobs..

UPDATE jomast
SET jomast.sono = jomast.QtNO
FROM jomast
Where Substring(Jomast.jobno,5,2) = '00'


Please Jelp !!!

TIA