CODEWITHSUNDEEP
sqlsql-server
SmashCode
SmashCode

Reputation: 4257

SQL Server Update with Case involving another table

I have 2 tables. One has order elements (OE) and one has project information(PO). There are many order elements to 1 project. The way the tables are set up, the project date is is in the PO and the Currency is in the OE. I need to update the Euro Exchange rate in the OE table. I am trying to do something like this

    UPDATE [OETest]
    SET [Euro Exchange Rate] = {
    CASE
        WHEN (DATEPART(month, PO.[Project Date Time]) = January)
        THEN 8.143296
        WHEN (DATEPART(month, PO.[Project Date Time]) = February)
        THEN 8.340111
    }
    FROM [POTest] PO, [OETest] OE       
    WHERE OE.[Currency] = 'YUAN'

But I am lost (This is one of many queries I have tried). Can anyone help me construct the necessary query and talk me through why it works?

This particular query is telling me there is Incorrect syntax near keyword CASE

To make it more clear what I am trying to accomplish: I have a column for the euro exchange rate in the OE table. I have the average monthly exchange rate that I obtained from a website (not in the table). I want to set this exchange rate column based on the month of the project and the currency. I am going to handle each currency in separate queries so the Yuan is the only currency I'm worried about for this query. The month is in the PO table. I need to use the month from the PO table in the case statement.

Upvotes: 0

Views: 3950

Answers (2)

Dubrovnic
Dubrovnic

Reputation: 71

What exactly seems to be the problem?

I can see many problems with the query. What are the sql errors you are getting?

IN any case, if I was to try to do the same as what i 'think' you are doing, I would do this:

UPDATE [OETest] OE
SET [Euro Exchange Rate] = 
(SELECT CASE WHEN (DATEPART(month, PO.[Project Date Time]) = 'January')
    THEN 8.143296
    ELSE 0.000000
END ExchageRate
FROM [POTest] PO)      
WHERE OE.[Currency] = 'YUAN'

What I am trying to do here is to select the result I want after the SET block. In the CASE statement, I have added an else in the event that I dont get a result from the date part.

The WHERE part is should be a WHERE condition on the UPDATE clause as you need to tell it what records you want to update, unless you want to update all records in that OETest table.

So in summary you are saying

Update the Euro Exchange Rate field with the value from the POTest Table when the month part of the Project Date Time field is equal to January, and update records in the OETest table where the Currency value is 'YUAN'.

Note, that you might need to have a WHERE clause in the SELECT statement as it might return multiple records and you only want one is order to get the Project Date Time field. At a guess, you might want to add a where statement such as follows:

UPDATE [OETest] OE
SET [Euro Exchange Rate] = 
(SELECT CASE WHEN (DATEPART(month, PO.[Project Date Time]) = 'January')
    THEN 8.143296
    ELSE 0.000000
END ExchageRate
FROM [POTest] PO WHERE PO.ProjectID = 100)      
WHERE OE.[Currency] = 'YUAN'

Let me know if this helps.

Regards JT

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

UPDATE OE -- the alias here rather than the base table name
   SET [Euro Exchange Rate] = 8.143296
  FROM [POTest] PO
  JOIN [OETest] OE ON OE.project_id = PO.project_id -- you need a link       
 WHERE OE.[Currency] = 'YUAN'
    -- the following date range represents January this year
    AND PO.[Project Date Time] >= '20120101'
    AND PO.[Project Date Time] <  '20120201'

You only need a case statement if you need different values depending on different dates

UPDATE OE -- the alias here rather than the base table name
   SET [Euro Exchange Rate] =
       CASE Month(PO.[Project Date Time])
            when 1 then 8.143296
            when 2 then 7.143296
            when 3 then 7.743296
            END
  FROM [POTest] PO
  JOIN [OETest] OE ON OE.project_id = PO.project_id -- you need a link       
 WHERE OE.[Currency] = 'YUAN'
    -- the following date range represents 3 months this year
    AND PO.[Project Date Time] >= '20120101'
    AND PO.[Project Date Time] <  '20120401'

Upvotes: 5

Related Questions