CODEWITHSUNDEEP
sqlsql-update
user2587986
user2587986

Reputation: 99

How to update a field in the same table

I need update a field with the same table. In the example below The RewRate is set correct in Co 1 , then I need updated the NewRate for Co 4, 10 and 16. based on the EDL Type, EDL Code. Thanks

enter image description here

This code is obviously wrong, but i just do not know how to fix it. sorry

  UPDATE       PRCI
   SET   NewRate = (select NewRate from PRCI  where PRCI.PRCo=1 and Craft ='xxx')

Sorry for the confusing. I need update EDL Code300 NewRate 0.05, EDL Code700 NewRate 5.3 EDL Code701 NewRate 3.7 EDL Code707 NewRate 0.78 EDL Code714 NewRate 3

For company 4 10 and 16

Upvotes: 1

Views: 88

Answers (3)

Ashish Charan
Ashish Charan

Reputation: 2387

There you go.

UPDATE t1 
SET t1.NewRate=t2.NewRate
FROM PRCI t1 
INNER JOIN PRCI t2
ON t1.EDLType=t2.EDLType
AND t1.EDLCode=t2.EDLCode
WHERE t1.PRCo!='1';

Upvotes: 2

Farkas Csaba
Farkas Csaba

Reputation: 1

What is your goal? It is not clear... Please describe the task more detailed.

Your sql is wrong, because the sub select returns multiple results, but only single result is expected... You may create a stored procedure or anonym block with a cursor. Make that cursor for select the distincted PRCo,Craft,EDL Type, EDL Code. Then fetch the key values and update the NewRate for each fetched keys with the calculated value.4

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

You can change 

and Craft ='xxx'

to 

and EDLCode = 714.

As mentioned in the comments, a where clause might be appropriate.

Upvotes: 0

Related Questions