Visual FoxPro Update Query

[jmoreno]

Please help!

I want to run an update query to match the service price (serprice) of a specified service code ("1") from another ("2") in the same table.

Here is a sample that doesn't work.

UPDATE t1 ;
SET t1.serprice = t2.serprice ;
FROM sertable t1 ;
JOIN sertable t2 ;
ON t1.customer = t2.customer ;
AND t1.sercode = "1" ;
AND t2.sercode = "2"

Any help will be greatly appreciated!

[srinika]

can u post some sample data pls

also the expected data after running ur Update

[jmoreno]

srinika, here is a sample data...

CustNo - SerCode - SerPrice
100001 - 1 - 1.19
100001 - 2 - 1.55
100001 - 3 - 0.75
100002 - 1 - 1.04
100002 - 2 - 1.21
100003 - 1 - 2.35
100003 - 2 - 1.89
100003 - 3 - 1.47

and so on... each customer has dozens of SerCodes

Expected data

CustNo - SerCode - SerPrice
100001 - 1 - 1.55
100001 - 2 - 1.55
100001 - 3 - 0.75
100002 - 1 - 1.21
100002 - 2 - 1.21
100003 - 1 - 1.89
100003 - 2 - 1.89
100003 - 3 - 1.47

Thanks for your help!

[srinika]

I'm a bit busy, So my answer is a work around.

Let me know if u want what is exactly asked.

My work around is not updating but Deleting & Inserting

Before doing anything backup ur table,
Run the following 2 quries to c whether u get the desired results
Then do the Delete / Insert Operation

The 2 queries to see the data as ur expectation:
Select * from sertable where sercode = '1' and CustNo in (Select CustNo from sertable b where sercode = '2')
Select * from sertable where sercode = '2' and CustNo in (Select CustNo from sertable b where sercode = '2')

The Delete Statement:
Delete from sertable where sercode = '1' and CustNo in (Select CustNo from sertable b where sercode = '2')

The Insert Statement:
Insert into Sertable
Select * from sertable where sercode = '2' and CustNo in (Select CustNo from sertable b where sercode = '2')

I know that there is an Update Statement which does both at the same time , thus more efficient, but I'm Sorry I'm busy this weekend.

[srinika]

OK I found a way

Update sertable
set SerPrice = b.Serprice
from (Select * from sertable where sercode = '2' ) b
where sertable.CustNo = b.custNo and sertable.sercode = '1' and b.sercode = '2'

Good Luck