sql server - HELP with update statement

Hi..
I need to update a table   I have a field called  HIGHEST_OF_3
and 3 sales fields,  SALE_1, SALE_2, SALE_3
I need to update the value of the HIGHEST_OF_3 with the largest value of SALES_1, or SALES_2 or SALES_3.

table looks something like this

customer id             sale 1         sale 2          sale 3              highest
1                               40               10                  90
2                              100                0                  30


thx
LVL 1
JElsterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Try this:

UPDATE myTable
SET highest_of_3 = (SELECT MAX(v) FROM (VALUES (sale_1), (sale_2), (sale_3)) AS VALUE(v))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JElsterAuthor Commented:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'VALUES'.
0
dsackerContract ERP Admin/ConsultantCommented:
If you're on MS SQL 2008 or higher, that works. Check your syntax. You must have parentheses like my example.

I just tested it with a temp table.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks like VALUES was introduced in SQL 2008 R2, so if you're running straight 2008 it might not be available to you.

If you can use it this might work..
SELECT Customer_id, sale_1, sale_2, sale_3,  
   (SELECT MAX(val) FROM (VALUES (sale_1), (sale_2), (sale_3)) AS value(val)) AS MaxVal 
FROM ENS_DIMENSIONALSTAB

Open in new window

Otherwise, the old-fashioned way would be to hoggle a subquery...
SELECT e.customer_id, sale_1, sale_2, sale_3, max_table.max_value 
FROM ENS_DIMENSIONALSTAB e
JOIN (
   SELECT customer_id, max(v) as max_value
   FROM (
      SELECT customer_id, sale_1 as v FROM ENS_DIMENSIONALSTAB
      UNION ALL
      SELECT customer_id, sale_2 FROM ENS_DIMENSIONALSTAB 
      UNION ALL
      SELECT customer_id, sale_3 FROM ENS_DIMENSIONALSTAB ) a
   GROUP BY customer_id) max_table ON e.customer_id = max_table.customer_id

Open in new window

0
JElsterAuthor Commented:
Sorry the server is sql2000...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.