Subquery in UPDATE Query 1

[bajo71]

Hello,
Upon trying to write the following subquery in the SELECT list of an UPDATE query, I get the following error messages: "...is not a valid name. Make sure that it does not include invalid characters or punctuation." Or "...Too many characters..."

However, when changing to a SELECT query, it runs fine. Is there some special syntactical convention that I am missing, or is it simply not possible to run a subquery within an Update?

Thanks much....ricky

BEGINNING_AMTSELECT ENDING_AMT
FROM AL_MAN_METRIC_FINANCIAL_DELTA_FULL m2
WHERE m2.EFFECTIVE_DATE=Format(DateAdd("m", -1, AL_MAN_METRIC_FINANCIAL_DELTA_FULL.EFFECTIVE_DATE), "YYYY-MM-DD") AND m2.SEGMENT_ID=AL_MAN_METRIC_FINANCIAL_DELTA_FULL.SEGMENT_ID)

 

[Remou]

The SQL you show is not suitable for an update query. It is selecting a sigle field with an alias BEGINNING_AMT. It is best to change to SQL view and post the SQL here.

 

[bajo71]

OK, here's the complete SQL view...

UPDATE AL_MAN_METRIC_FINANCIAL_DELTA_FULL AS m1 SET m1.BEGINNING_AMT = (SELECT ENDING_AMT FROM AL_MAN_METRIC_FINANCIAL_DELTA_FULL m2 WHERE m2.EFFECTIVE_DATE=Format(DateAdd("m", -1, m1.EFFECTIVE_DATE), "YYYY-MM-DD") AND m2.SEGMENT_ID=m1.SEGMENT_ID);


Trying to run this still yields an "Operation must use an updateable query" error.

 

[bajo71]

Sorry, here's the correct code; the previous erroneously placed the Select statement in the Update To: field.

UPDATE AL_MAN_METRIC_FINANCIAL_DELTA_FULL AS m1 SET m1.BEGINNING_AMT = [m1].[ENDING_AMT]
WHERE (((m1.BEGINNING_AMT)=(SELECT ENDING_AMT FROM AL_MAN_METRIC_FINANCIAL_DELTA_FULL m2 WHERE m2.EFFECTIVE_DATE=Format(DateAdd("m", -1, m1.EFFECTIVE_DATE), "YYYY-MM-DD") AND m2.SEGMENT_ID=m1.SEGMENT_ID)));


However, this yields zero records updated and the Ending_Amt field is never null.

 

[bajo71]

And the data looks like this...

EFFECTIVE_DATE SEGMENT_ID BEGINNING_AMT ENDING_AMT 2008-01-01 111261 1473.9
2008-02-01 111261 1492.6
2008-03-01 111261 1415.6
2008-04-01 111261 1449.9
2008-05-01 111261 1459.8
2008-06-01 111261 1454.8

 

[bajo71]

Not sure what happened after I posted but the Beginning_AMT field is empty and the Ending_Amt starts with 1473.9, 1492.6 etc...

 

[Golom]

First if all, this

SET m1.BEGINNING_AMT = m1.ENDING_AMT

is setting BEGINNING_AMT to the value of ENDING_AMT on the same record. That's not the implication of your initial post where you were attempting to set it to the value returned by the sub-query.

It this what you were intending to do?

UPDATE AL_MAN_METRIC_FINANCIAL_DELTA_FULL AS m1 INNER JOIN
       AL_MAN_METRIC_FINANCIAL_DELTA_FULL As m2 
       ON  m2.EFFECTIVE_DATE = DateAdd("m", -1, m1.EFFECTIVE_DATE) 
       AND m2.SEGMENT_ID     = m1.SEGMENT_ID
       AND m1.BEGINNING_AMT  = m2.ENDING_AMT 

SET m1.BEGINNING_AMT = m2.ENDING_AMT

 

[bajo71]

OK, Let me backup. The following SQL statement does exactly what I want it to, however it is a Select query AND the subquery is embedded in the SELECT list, as opposed to an Update query and the subquery embedded in the criteria.

I now need to parlay this logic into an UPDATE query and to my knowledge you cannot create a new field as this would violate the Update criteria, so I created a field Beginning_Amt in a previous make-table to be populated in this Update, but I cannot get it to work.

SELECT (SELECT ENDING_AMT FROM AL_MAN_METRIC_FINANCIAL_DELTA_FULL m2 WHERE m2.EFFECTIVE_DATE=Format(DateAdd("m", -1, m1.EFFECTIVE_DATE), "YYYY-MM-DD") AND m2.SEGMENT_ID=m1.SEGMENT_ID) AS END_AMT
FROM AL_MAN_METRIC_FINANCIAL_DELTA_FULL AS m1;

 

[bajo71]

First if all, this

SET m1.BEGINNING_AMT = m1.ENDING_AMT

is setting BEGINNING_AMT to the value of ENDING_AMT on the same record. That's not the implication of your initial post where you were attempting to set it to the value returned by the sub-query.

-You are correct, I do not want to SET m1.BEGINNING_AMT = m1.ENDING_AMT.

Essentially, BEGINNING_AMT is an empty field that will need to be populated with the previous month's(ie EFFECTIVE_DATE)ENDING_AMT.

It is accomplished easily in a SELECT but not UPDATE.

 

[PHV]

What about this ?
UPDATE AL_MAN_METRIC_FINANCIAL_DELTA_FULL AS m1 INNER JOIN AL_MAN_METRIC_FINANCIAL_DELTA_FULL As m2
ON m2.EFFECTIVE_DATE = DateAdd("m", -1, m1.EFFECTIVE_DATE) AND m2.SEGMENT_ID = m1.SEGMENT_ID
SET m1.BEGINNING_AMT = m2.ENDING_AMT

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bajo71]

PHV:
I don't think it likes the DateAdd in the join. Using the suggested code, I receive a "Type mismatch in Expression" error.

 

[PHV]

What is the data type of EFFECTIVE_DATE ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bajo71]

It is in fact text. So would I be able to use something like...Format(DateAdd("m", -1, m1.EFFECTIVE_DATE), "YYYY-MM-DD") ?

 

[PHV]

You may try this:

UPDATE AL_MAN_METRIC_FINANCIAL_DELTA_FULL AS m1 INNER JOIN AL_MAN_METRIC_FINANCIAL_DELTA_FULL As m2
ON CDate(m2.EFFECTIVE_DATE) = DateAdd("m", -1, m1.EFFECTIVE_DATE) AND m2.SEGMENT_ID = m1.SEGMENT_ID
SET m1.BEGINNING_AMT = m2.ENDING_AMT

If you get the "Operation must use an updateable query" error, then you may try this:

UPDATE AL_MAN_METRIC_FINANCIAL_DELTA_FULL
SET BEGINNING_AMT = DLookUp("ENDING_AMT","AL_MAN_METRIC_FINANCIAL_DELTA_FULL","EFFECTIVE_DATE='" & Format(DateAdd("m",-1,[EFFECTIVE_DATE]),"yyyy-mm-dd") & "' AND SEGMENT_ID=" & [SEGMENT_ID])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886