UPDATE within same table

[klmc]

Currently have table with following structure:
CY_DATE
PY_DATE
ACCOUNT
ACCT_UNIT
SUB_ACCT
DESCR
CY_AMT
CY_UNITS
PY_AMT
PY_UNITS

I am looking for a way to insert the PY_AMT and PY_UNITS. It is going to read in the CY_DATE, ACCOUNT, ACCT_UNIT, SUB_ACCT, DESCR, CY_AMT, and CY_UNITS from a file. The PY_DATE will be inserted from a cross reference table. I am going to have to take the PY_DATE and find where it matches a CY_DATE entry with all of the other fields matching each other. Is there an easy way to do this?

Any help is greatly appreciated.

 

[esdee]

something like :

update your_table set
py_amt = (select sum(py_amt)
from your_table where .... conditions)

should do

 

[klmc]

Nope, it doesn't return anything. I tried:

UPDATE LMSALESRPT
SET PY_AMT = (SELECT PY_AMT FROM LMSALESRPT
WHERE CY_DATE = PY_DATE
AND ACCOUNT = ACCOUNT
AND ACCT_UNIT = ACCT_UNIT
AND SUB_ACCT = SUB_ACCT
AND DESCR = DESCR)

Any other ideas? It'd greatly appreciative.

 

[klmc]

Oops, typo I meant that I tried:

UPDATE LMSALESRPT
SET PY_AMT = (SELECT CY_AMT FROM LMSALESRPT
WHERE CY_DATE = PY_DATE
AND ACCOUNT = ACCOUNT
AND ACCT_UNIT = ACCT_UNIT
AND SUB_ACCT = SUB_ACCT
AND DESCR = DESCR)

Thanks

 

[esdee]

oh, i see - you dont need a sum;
but your latest query seems reasonable - may be there really arent records matching your needs?

 

[klmc]

When I do

UPDATE LMSALESRPT
SET PY_AMT = (SELECT B.CY_AMT
	FROM LMSALESRPT B, LMSALESRPT A
	WHERE A.PY_DATE = B.CY_DATE
	AND A.ACCOUNT = B.ACCOUNT
	AND A.ACCT_UNIT = B.ACCT_UNIT
	AND A.SUB_ACCT = B.SUB_ACCT
	AND A.DESCR = B.DESCR)

I get an error that it is returning more than 1 result. I'm guessing I need to set this up somehow to loop through the records one at a time? Any suggestions on how to do this? Thanks in Advance

 

[MacLeod72]

Based on your query, do you mean:

UPDATE LMSALESRPT
SET PY_AMT = A.PY_AMT
FROM
    LMSALESRPT,
    LMSALESRPT A
WHERE
    LMSALESRPT.CY_DATE = A.PY_DATE
    AND LMSALESRPT.ACCOUNT = A.ACCOUNT
    AND LMSALESRPT.ACCT_UNIT = A.ACCT_UNIT
    AND LMSALESRPT.SUB_ACCT = A.SUB_ACCT
    AND LMSALESRPT.DESCR = A.DESCR

--Rob

 

[MacLeod72]

I meant

 SET PY_AMT = A.CY_AMT

--Rob

 

[klmc]

Perfect, thanks so much!!!!!!

Kelley