sql query help - UPDATE statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query help - UPDATE statement

Author

Topic

jcarrallo
Starting Member

35 Posts

Posted - 2014-06-12 : 17:26:06

Hi SQL Team,

I wonder if you can help me with this:

I want to update records in 1 table with the result of a select statement.

The table is called 'MPR_Portfolio_Transactions' and contains the following fields:

[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]

And the select statement is like this:

SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE

This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'

the key is PTR_SEQUENCE

Please help!!!
thanks in advanced.
Jay

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-12 : 17:29:28

Maybe this:


UPDATE m
SET PTR_ACUM = ACUMULADO
FROM MPR_portfolio_transactions m
JOIN 
(
	SELECT PTR_SEQUENCE, SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
	FROM MPR_portfolio_transactions
) dt
ON m.PTR_SEQUENCE = dt.PTR_SEQUENCE

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/