Novice SQL Update Question

Revised Query.. the [Id] column is unique to all records.. the query should return the correct value for CorEURUSD to both Symbol = EURUSD and Symbol = GBPUSD where the [Time] = [Time] values.

 CREATE TABLE [dbo].[Tck2](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Symbol] [varchar](35) NULL,
[Time] [datetime] NULL,
[CorEURUSD] [decimal](14, 10) NULL,
[CorEURUSD2] [decimal](14, 10) NULL
  ) ON [PRIMARY]


 INSERT [VT7STAB1].[dbo].[Tck2] ([Symbol],[Time],[CorEURUSD],[CorEURUSD2]) VALUES('EUR/USD', '2011-07-01 12:04:28.000', 0.8229, 0.6488)
 INSERT [VT7STAB1].[dbo].[Tck2] ([Symbol],[Time],[CorEURUSD],[CorEURUSD2]) VALUES('EUR/USD', '2011-07-01 12:26:17.000', 0.9427, 0.6558)
 INSERT [VT7STAB1].[dbo].[Tck2] ([Symbol],[Time],[CorEURUSD],[CorEURUSD2]) VALUES('EUR/USD', '2011-07-01 12:58:34.000', 0.7713, 0.5267)
 INSERT [VT7STAB1].[dbo].[Tck2] ([Symbol],[Time],[CorEURUSD],[CorEURUSD2]) VALUES('GBP/USD', '2011-07-01 12:04:28.000', 0, 0)
 INSERT [VT7STAB1].[dbo].[Tck2] ([Symbol],[Time],[CorEURUSD],[CorEURUSD2]) VALUES('GBP/USD', '2011-07-01 12:26:17.000', 0, 0)
 INSERT [VT7STAB1].[dbo].[Tck2] ([Symbol],[Time],[CorEURUSD],[CorEURUSD2]) VALUES('GBP/USD', '2011-07-01 12:58:34.000', 0, 0)

Running the following query in an attempt to copy the CorEURUSD column from Symbol - 'EUR/USD' into the resulting CorEURUSD column for Symbol = 'GBP/USD

update Tck2
set CorEURUSD = (
     select CorEURUSD
     from Tck2 T 
     where Symbol = 'EUR/USD')
 where Symbol = 'GBP/USD'

gave this error Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not p开发者_JAVA技巧ermitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

and when I used this revision..

update Tck2
set CorEURUSD = (
     select CorEURUSD
     from Tck2 T 
     where Symbol = 'EUR/USD')
 where Symbol = 'GBP/USD'
 and T.[Time] = [Time]

It throws this error.

Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "T.Time" could not be bound.

I hope that is better, sorry for the 'mass-confusion' please revise answers to match the above query and table which should be correct.

Just a guess based on loose specs and no sample data / desired results.

UPDATE t
    SET t.[CorEURUSD] = x.[CorEURUSD] 
    FROM dbo.TicksForex AS t
    INNER JOIN dbo.TicksForex AS x
    ON t.[id] = x.[id]
    WHERE 
        t.[Symbol] = 'GBP/USD'
        AND x.[Symbol] = 'EUR/USD';

EDIT 2011-07-03 Based on revised specs. Is [Time] really going to be your key for this type of update? Sounds risky. Anyway, since [Time] was the only way I could determine to join two rows based on your narrative and sample data, here is what I assume you mean (and I can also assume you only want to update CorEURUSD and not CorEURUSD2):

UPDATE t
    SET t.[CorEURUSD] = x.[CorEURUSD]
    FROM dbo.Tck2 AS t
    INNER JOIN dbo.Tck2 AS x
    ON t.[Time] = x.[Time]
    WHERE t.Symbol = 'GBP/USD'
    AND x.Symbol = 'EUR/USD';

Really wasn't that much of a modification really, I merely changed the join condition.

The error message is saying that your subquery:

SELECT [CorEURUSD] 
  FROM TicksForex T 
 WHERE [Symbol] = 'EUR/USD' 
   AND T.[id] = [id]

...is returning more than one value. Can't store more than one value in a single column. You could use an aggregate to get the highest of the values:

SELECT MAX([CorEURUSD])
  FROM TicksForex T 
 WHERE [Symbol] = 'EUR/USD' 
   AND T.[id] = [id]

...or the lowest:

SELECT MIN([CorEURUSD])
  FROM TicksForex T 
 WHERE [Symbol] = 'EUR/USD' 
   AND T.[id] = [id]

...but you didn't provide details for us to work with.

Three problems:

1. Missing parenthesis.

2. T.[id] = [id] condition does not make much sense, as it is equivalent to id = id, which is always true.

   The query below should work, with the assumption that Symbol is unique:

   update TicksForex 
   set [CorEURUSD] = (
           select CorEURUSD 
           from TicksForex 
           where Symbol = 'EUR/USD'
       )
   where Symbol = 'GBP/USD'
   

3. However, from what your error suggests Symbol is not unique.

   Possible ways to fix this:

   • remove the duplicates. I believe this is the best solution, but can't tell for sure based on the info from the question

   • take first value:

     update TicksForex 
     set CorEURUSD = (
         select top 1 CorEURUSD
         from TicksForex T 
         where Symbol = 'EUR/USD')
     where Symbol = 'GBP/USD'
     

   • take min, max, avg, etc value:

     update TicksForex 
     set CorEURUSD = (
         select max(CorEURUSD)
         from TicksForex T 
         where Symbol = 'EUR/USD')
     where Symbol = 'GBP/USD'
     

   Basically you either need to make sure there are no duplicates, or to specify which exactly duplicate row should be used as the source of CorEURUSD value.

The way I tested this:

1. Created table using your script.

2. Filled table with two lines:

   insert TicksForex
   values('GBP/USD', 10, 20, 100)
   
   insert TicksForex
   values('EUR/USD', 30, 40, 200)
   

3. Ran the query as is:

   update TicksForex 
   set CorEURUSD = 
       select CorEURUSD
       from TicksForex T 
       where Symbol = 'EUR/USD' and T.[id] = [id]
   where Symbol = 'GBP/USD'
   

   Error:

   Msg 156, Level 15, State 1, Line 3
   Incorrect syntax near the keyword 'select'.
   Msg 156, Level 15, State 1, Line 6
   Incorrect syntax near the keyword 'where'.
   

4. Added parenthesis and removed and T.[id] = [id]:

   update TicksForex 
   set CorEURUSD = (
       select CorEURUSD
       from TicksForex T 
       where Symbol = 'EUR/USD')
   where Symbol = 'GBP/USD'
   

   Worked.

5. Added duplicate row:

   insert TicksForex
   values('EUR/USD', 50, 60, 300)
   

6. Ran script again:

   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.
   

7. Tried all 3 solutions from my answer above - worked.

[EDIT] After sample data is provided.

If the time is absolutely matching, you can do this:

update t1 
set CorEURUSD = t2.CorEURUSD, CorEURUSD2 = t2.CorEURUSD2
from Tck2 t1 
    join Tck2 t2 on
        t1.Time = t2.Time
where 
    t1.Symbol = 'GBP/USD' and
    t2.Symbol = 'EUR/USD' 

If the time is not completely matching, you can do this:

;with 
cte1 as
(
    select *, row_number() over (order by Time) RowNumber
    from Tck2
    where Symbol = 'EUR/USD'
),
cte2 as
(
    select *, row_number() over (order by Time) RowNumber
    from Tck2
    where Symbol = 'GBP/USD'
)
update cte2
set CorEURUSD = cte1.CorEURUSD, CorEURUSD2 = cte1.CorEURUSD2
from cte1 
    join cte2 on
        cte1.RowNumber = cte2.RowNumber

First, you need parenthesis around the subquery or you would have another error thrown:

UPDATE TicksForex 
   SET [CorEURUSD] = ( SELECT [CorEURUSD] 
                        FROM TicksForex T 
                       WHERE [Symbol] = 'EUR/USD' 
                     )
WHERE [Symbol] = 'GBP/USD' ;

Second, the error suggests that there are more than one rows with [Symbol] = 'EUR/USD'. This query will show a number more than 1:

SELECT COUNT(*) 
FROM [TicksForex] 
WHERE [Symbol] = 'EUR/USD' ;

Now I got your question

here is your query:

update a
set a.[CorEURUSD] = b.[CorEURUSD]
from [dbo].[Tck2] as a
join [dbo].[Tck2] as b on a.[Time] = b.[Time]
where a.[Symbol] = 'GBP/USD' and b.[Symbol]= 'EUR/USD'