Inter Table Multiplication and Result (Newbie)

I want to multiply a column (Units) for all transactions in the current
table by a price from another table and reflect the result a column in the
'Units' table. How would I do this and update this daily using SQL server.
The transactions and pricing are matched by a 'Date' field.
Thanks for any help or direction.http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
> I want to multiply a column (Units) for all transactions in the current
> table by a price from another table and reflect the result a column in the
> 'Units' table. How would I do this and update this daily using SQL server.
> The transactions and pricing are matched by a 'Date' field.
> Thanks for any help or direction.
>|||Adam
The SQL statement I am using is below but I get the error that is shown
thereafter. What I am really trying to achieve is as documented int eh
second statement.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*10
WHERE [Dates]=[dbo].[Prices].[Dates]
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'dbo.Prices' does not match with a table name or alias
name used in the query.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
Thanks for an assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
>|||Total guess, since you still haven't provided DDL (I don't know what the
keys are):
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[Prices].[numbers]*10
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
If this still doesn't do it, please post DDL and sample data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
> Adam
> The SQL statement I am using is below but I get the error that is shown
> thereafter. What I am really trying to achieve is as documented int eh
> second statement.
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*10
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'dbo.Prices' does not match with a table name or alias
> name used in the query.
>
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Thanks for an assistance.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
>|||Adam
I hope I have the relevant info correct as requested. Thanks again.
CREATE TABLE [Prices] (
[Prices] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [numbers] (
[numbers] [float] NULL ,
[number_result] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(100, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(200, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(300, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(400, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(2.5, 38564)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(3.2, 38565)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Total guess, since you still haven't provided DDL (I don't know what the
> keys are):
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[Prices].[numbers]*10
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
>
> If this still doesn't do it, please post DDL and sample data.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
>|||Try:
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
By the way, what are these floating-point "dates" ?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> Adam
> I hope I have the relevant info correct as requested. Thanks again.
>
> CREATE TABLE [Prices] (
> [Prices] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [numbers] (
> [numbers] [float] NULL ,
> [number_result] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(100, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(200, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(300, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(400, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(2.5, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(3.2, 38565)
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
alias
message
in
>|||Adam
I get the error 'Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near ')'.
I have tried adding another ')' after line and then get the error message
Invalid object name 'dbo.Prices'
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%235WjsUMqFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Try:
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
>
> By the way, what are these floating-point "dates" ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> alias
> message
> in
>|||On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:

>Adam
>I get the error 'Server: Msg 170, Level 15, State 1, Line 13
>Line 13: Incorrect syntax near ')'.
Hi dl,
There's a small typo in Adam's message:
should be
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I changed this and got the error message 'Invalid object name 'dbo.Prices'.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:8uapg19u9imvq32ca4a1nc2ujhdq84186o@.
4ax.com...
> On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:
>
> Hi dl,
> There's a small typo in Adam's message:
>
> should be
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)