Update from Select Statment
-
What am I doing wrong? Newbie.
I want to update AxysPriceFile table and update CurrentPX from my Select Statement set AS Moxy.
So CurrentPX = Moxy.Price
Where Type = Moxy.SecType
Where Ticker = Moxy.Symbol
UPDATE AxysPriceFile
SET CurrentPX = Moxy.Price
WHERE Type = Moxy.SecType AND Ticker = Moxy.Symbol IN
(SELECT SecType, Symbol, Price
FROM SRV5MOXY.moxy60.dbo.moxyprice AS moxyprice_1
WHERE (SecType = 'csus') AND (PriceDate > CONVERT(nvarchar(10), GETDATE(), 101))) AS Moxy)
MY ERROR: It's saying it's near my 'IN' but I don't know what I am doing wrong.
Thanks in advance.
-
1. Wrong syntax
2. Danger of converting date into non ISO formatted date string (code 101, means that potentially you may have something like 05/06/2014. Now tell me is it 5th of June or 6th of May? - it depends where you are from... Always use ISO format: (eg. 112). In your case, if the PriceDate is datetime column and the purpose of converting is to get rid off time part, just use the following:
UPDATE APF
SET CurrentPX = Moxy.Price
FROM AxysPriceFile AS APF
JOIN (
SELECT SecType, Symbol, Price
FROM SRV5MOXY.moxy60.dbo.moxyprice AS moxyprice_1
WHERE SecType = 'csus'
AND PriceDate > CAST(GETDATE() AS DATE)
) AS Moxy
ON APF.[Type] = Moxy.SecType
AND APF.Ticker = Moxy.Symbol
Please note the query layout: it does help readability....
-
If you format your code, it should be clear.
UPDATE AxysPriceFile
SET CurrentPX = Moxy.Price
WHERE Type = Moxy.SecType
AND Ticker = Moxy.Symbol
IN /*What column should be compared?*/
(SELECT SecType, Symbol, Price
FROM SRV5MOXY.moxy60.dbo.moxyprice AS moxyprice_1
WHERE (SecType = 'csus')
AND (PriceDate > CONVERT(nvarchar(10), GETDATE(), 101)))
AS Moxy) /*What's this for?*/
-
SSCrazy - Thanks for your help and I'm pretty close. When I execute your code, I get Invalid Object Name 'APF' and I don't know why. When I look at the Design view, it shows me an APF table by itself.
UPDATE APF
SET APF.CurrentPX = Moxy.Price
FROM AxysPriceFile AS APF INNER JOIN
(SELECT SecType, Symbol, Price
FROM SRV5MOXY.moxy60.dbo.moxyprice AS moxyprice_1
WHERE (SecType = 'csus') AND (PriceDate > CAST(GETDATE() AS DATE))) AS Moxy ON APF.Type = Moxy.SecType AND APF.Ticker = Moxy.Symbol CROSS JOIN
APF
Thanks again!
-
It looks like you did not get all the code copied.
-
UPDATE APF
SET APF.CurrentPX = Moxy.Price
FROM AxysPriceFile AS APF INNER JOIN
(SELECT SecType, Symbol, Price
FROM SRV5MOXY.moxy60.dbo.moxyprice AS moxyprice_1
WHERE (SecType = 'csus') AND (PriceDate > CAST(GETDATE() AS DATE))) AS Moxy ON APF.Type = Moxy.SecType AND APF.Ticker = Moxy.Symbol CROSS JOIN
APF
-
Look at Eugene Elutin post.
In your code the CROSS JOIN APF is not correct. Remove it and you might get something back.
In Eugene's post he had a WHERE clause in the area you have the CROSS JOIN.
-
I changed the date convert back and it worked.
UPDATE APF
SET CurrentPX = Moxy.Price
FROM AxysPriceFile AS APF
JOIN (
SELECT SecType, Symbol, Price
FROM SRV5MOXY.moxy60.dbo.moxyprice AS moxyprice_1
WHERE (SecType = 'csus') AND (PriceDate > CONVERT(nvarchar(10), GETDATE(), 101))
) AS Moxy
ON APF.Type = Moxy.SecType
AND APF.Ticker = Moxy.Symbol
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply