UPDATE between tables

[klmc]

I am creating a table that is pulling information from a table that is already in existance. The current table contains sales information for a given date. The new table is going to take the current date and compare it to the sales from a year ago. The structure for the new table is CY_DATE, PY_DATE, ACCOUNT, ACCT_UNIT, SUB_ACCT, CY_AMT, CY_UNITS, PY_AMT, PY_UNITS.

I have gotten all fields populated except for the PY_AMY and PY_UNITS. It is a bit tricky because I am taking the date in PY_DATE and turning around and using the same table that was used to get the current year data. You also have to match the ACCOUNT, ACCT_UNIT, and SUB_ACCOUT exactly between the 2 tables. If anyone has any suggestions I would be so greatful. I have tried UPDATE with both INNER JOINS and WHERE and can't figure it out.

 

[hneal98]

Can you paste here what you have tried?

 

[klmc]

Here is what I have tried. In the script SLTRANS is the original table and LMSALESRPT is the new one. I added the Description field to match on since the ACCT_UNIT, ACCOUNT, and SUB_ACCOUNT could be the same for several entries and the DESCRIPTION would be the only thing that differentiates the entries. Thanks.

UPDATE LMSALESRPT
SET LMSALESRPT.PY_AMT = SLTRANS.BASE_AMOUNT,
    LMSALESRPT.PY_UNITS = SLTRANS.UNITS_AMOUNT
FROM SLTRANS
WHERE LMSALESRPT.PY_DATE = SLTRANS.POSTING_DATE
AND LMSALESRPT.ACCT_UNIT = SLTRANS.ACCT_UNIT
AND LMSALESRPT.ACCOUNT = SLTRANS.ACCOUNT
AND LMSALESRPT.SUB_ACCT = SLTRANS.SUB_ACCOUNT
AND LMSALESRPT.DESCR = SLTRANS.DESCRIPTION


UPDATE LMSALESRPT
SET LMSALESRPT.PY_AMT = SLTRANS.BASE_AMOUNT,
    LMSALESRPT.PY_UNITS = SLTRANS.UNITS_AMOUNT
FROM SLTRANS
INNER JOIN LMSALESRPT ON (LMSALESRPT.PY_DATE = SLTRANS.POSTING_DATE) 
AND (LMSALESRPT.ACCT_UNIT = SLTRANS.ACCT_UNIT)
AND (LMSALESRPT.ACCOUNT = SLTRANS.ACCOUNT)
AND (LMSALESRPT.SUB_ACCT = SLTRANS.SUB_ACCOUNT)
AND (LMSALESRPT.DESCR = SLTRANS.DESCRIPTION)

 

[jby1]

Try this, it should work for you ...

UPDATE LMSALESRPT
SET LMSALESRPT.PY_AMT = (SELECT SLTRANS.BASE_AMOUNT
	FROM SLTRANS
	WHERE LMSALESRPT.PY_DATE = SLTRANS.POSTING_DATE
	AND LMSALESRPT.ACCT_UNIT = SLTRANS.ACCT_UNIT
	AND LMSALESRPT.ACCOUNT = SLTRANS.ACCOUNT
	AND LMSALESRPT.SUB_ACCT = SLTRANS.SUB_ACCOUNT
	AND LMSALESRPT.DESCR = SLTRANS.DESCRIPTION),
    LMSALESRPT.PY_UNITS = (SELECT SLTRANS.UNITS_AMOUNT
	FROM SLTRANS
	WHERE LMSALESRPT.PY_DATE = SLTRANS.POSTING_DATE
	AND LMSALESRPT.ACCT_UNIT = SLTRANS.ACCT_UNIT
	AND LMSALESRPT.ACCOUNT = SLTRANS.ACCOUNT
	AND LMSALESRPT.SUB_ACCT = SLTRANS.SUB_ACCOUNT
	AND LMSALESRPT.DESCR = SLTRANS.DESCRIPTION)
WHERE EXISTS (SELECT 1
	FROM SLTRANS
	WHERE LMSALESRPT.PY_DATE = SLTRANS.POSTING_DATE
	AND LMSALESRPT.ACCT_UNIT = SLTRANS.ACCT_UNIT
	AND LMSALESRPT.ACCOUNT = SLTRANS.ACCOUNT
	AND LMSALESRPT.SUB_ACCT = SLTRANS.SUB_ACCOUNT
	AND LMSALESRPT.DESCR = SLTRANS.DESCRIPTION)