SQL server 2012 Need help with an Update from a Select

[DougP]

This requires the Group by since the RecordID's are out of order and the data is synced by Record ID.
The Error is
"Msg 8114, Level 16, State 5, Line 15
Error converting data type nvarchar to numeric."

UPDATE  [dbo].[HistData]
SET  [TCKR%]    = CAST(Ticker as decimal(9,6))
FROM   [dbo].[HistData] 
    Where Exists (Select E.RecordID as TheRecordID,
(L.Stock_Close -  E.Stock_Close)/L.Stock_Close as Ticker
from HistData as L
inner join HistData AS E on L.RecordID = E.RecordID + 1
Where L.RecordID= L.RecordID
Group by E.RecordID,L.Stock_Close,E.Stock_Close)

This Select works just fine but having trouble making it into an Update.

Select E.RecordID as TheRecordID, L.Stock_Close, E.Stock_Close,
(L.Stock_Close -  E.Stock_Close)/L.Stock_Close 
from HistData as L
inner join HistData AS E on L.RecordID = E.RecordID + 1
Where L.RecordID= L.RecordID
Group by E.RecordID,L.Stock_Close,E.Stock_Close

See attachment for sample data.

DougP

 

[Olaf Doschke]

1. You don't relate the [dbo].[HistData] to the subquery
2. The CAST(Ticker as decimal(9,6)) is failing. You may have NULL values in HistData.Ticker or any other value not converting to a decimal, that's what the error says in detail
3. (sidenote): Where L.RecordID= L.RecordID does not make much sense.

Bye, Olaf.

 

[DougP]

Can someone just take the Select and make that work as an Update for it?

UPDATE  [dbo].[HistData]
SET  [TCKR%] = (Select)

I put the CAST in for another error
Error converting data type nvarchar to numeric.

DougP

 

[gmmastros]

What do you get when you run this query?

Select	Data_Type
From	Information_Schema.Columns
Where	Table_Name = 'HistData'
	And Column_Name = 'Stock_Close'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

>SET [TCKR%] = (Select)
That would only work, if that subselct only has one value in one column per dbo.HistData row you try to update.

What should become the value of TCKR%?

Bye, Olaf.

 

[DougP]

gmmastros, Stock_Close is decimal or specifically decimal(6,2) and TCKR% is decimal(9,6).

OlafDoschke, if I change it to this:

Update [dbo].[HistData]
SET [TCKR%] = (Select (L.Stock_Close -  E.Stock_Close)/L.Stock_Close 
from HistData as L
inner join HistData AS E on L.RecordID = E.RecordID + 1
Where L.RecordID= L.RecordID
Group by E.RecordID,L.Stock_Close,E.Stock_Close)

I get Error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

There may be a better way to do this.
This is an Excel sheet I am trying to convert to SQL server, see new attachment: Excel_TCKR_formula.JPG
So to calculate TCKR% it's taking the Stock_close of the existing row minus the Stock_Close of the Prvious row, divided by the Stock_close of the previous row, or (F2-F3)/F3) (see attachment)
If there is a better way to do this and Update the TCKR% column, Great
Also the import of the Excel data did not put the rows in order (RecordID column which data type INT, identity seed(1,1)), in a default sort. So if I need to reimport the Excel data during import or do something to the table now let me know? If the Record ID's are in the same order as the actual rows from the Excel import then maybe this becomes a lot simpler? So that is why I am using the 'Order by' or 'Group by' to keep things in order. And that is why the L.RecordID = L.RecordID. Otherwise the formula gets the wrong rows of data and calculates incorrectly since its not getting the correct previous row.

Update [dbo].[HistData]
SET [TCKR%] = (Select (L.Stock_Close -  E.Stock_Close)/L.Stock_Close 
from HistData as L
inner join HistData AS E on L.RecordID = E.RecordID + 1
Where L.RecordID= L.RecordID
Group by E.RecordID,L.Stock_Close,E.Stock_Close)

I get Error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


DougP

 

[DougP]

I found the problem!!!!
After much interrogation of the table... I found out there were 3 indexes on the TABLE, which meant the raw data was out of sync. as shown in bottom part of this attachment, and why I added the Group By: shown in the top of the attachment.
I deleted two of the indexes which made the raw sort work like it should and the following simplified Update statement now works Great!
Sorry guys... Thanks everyone for your help

UPDATE L
SET [TCKR%] = (L.Stock_Close -  E.Stock_Close)/L.Stock_Close 
FROM HistData as L
JOIN HistData AS E on L.RecordID = E.RecordID + 1
Where L.RecordID= L.RecordID

DougP

 

[Olaf Doschke]

If you're sure you get what you want, then fine.

The error you got last (Subquery returned more than 1 value) was just technically saying, what I already said in case you'd do an update with a COLUMN=(SELECT value FROM...): That SELECT has to have 1 row, 1 column. Because you only set a single column of a single row. I said literally:

if that subselct only has one value in one column per dbo.HistData row you try to update

That additionally means that subselect would need to be related to HistData.

What you do right now is updating a TCKR% with an expression taking values of a row (E) and the next row (L). Nothing wrong about that, but that's totally different now from testing EXISTS and even more different than updating from the Ticker column. Nobody could have even guessed that.

Bye, Olaf.