CODEWITHSUNDEEP
sqlsql-server
Sequel3
Sequel3

Reputation: 75

Set Query with a case statement

I am trying to execute this query

But I get a syntax error

Running MS SQL Management Studio

Incorrect syntax near the keyword 'CASE'.

My Query

update [Test$ABC]
Set [Unit Price] =(

CASE WHEN [Item No_]= '12345' THEN '445'

CASE WHEN [Item No_]= '67890' THEN '645'

END

)

where [Item No_] in ('12345','67890')
and [Sales Start Date]='01/Apr/19'
and [Store No_]=''

Upvotes: 0

Views: 105

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I would write this as:

update [Test$ABC]
    set [Unit Price] = (CASE WHEN [Item No_] = 12345 THEN 445
                             WHEN [Item No_] = 67890 THEN 645
                             ELSE [Unit Price]
                        END)
where [Item No_] in (12345, 67890) and
      [Sales Start Date] = '2018-04-01' and
      [Store No_] = '';

Notes:

  • [Item No_] is probably a number, so only compare to numbers.
  • Price is almost certainly a number, so single quotes are not needed.
  • The ELSE can save problems if you have a typo. If the list in the CASE doesn't match the WHERE, then this will not update other rows.
  • Dates should be stored as dates, and comparison values should use standard formats -- YYYY-MM-DD.

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

You have an incorrect syntax, your code should be like

UPDATE [Test$ABC]
SET [Unit Price] = CASE [Item No_] WHEN '12345' THEN '445'
                                   WHEN '67890' THEN '645'
                   END

where [Item No_] in ('12345','67890')
and [Sales Start Date]='01/Apr/19'
and [Store No_]=''

Please visit and read CASE from the docs online.

Upvotes: 0

Santhana
Santhana

Reputation: 417

Please find the syntax for SQL SERVER CASE statement,

CASE should begins keyword 'CASE' and ends with Keyword 'END'

For your code :

remove case in below line

--CASE WHEN [Item No_]= '67890' THEN '645'

/****************************************/

CASE expression

WHEN value_1 THEN result_1

WHEN value_2 THEN result_2

... WHEN value_n THEN result_n

ELSE result

END

/****************************************/

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below, i think unit price is int data type so i ignored quote there 

update [Test$ABC]
Set [Unit Price] =(

CASE WHEN [Item No_]= '12345'
 THEN 445   
 else 645  
END

)    
where [Item No_] in ('12345','67890')
and [Sales Start Date]='01/Apr/19'
and [Store No_]=''

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You just need one case expression :

update [Test$ABC]
Set [Unit Price] =(CASE WHEN [Item No_] = '12345' THEN '445'
                        WHEN [Item No_] = '67890' THEN '645'
                   END)
where [Item No_] in ('12345','67890') and 
      [Sales Start Date] = '01/Apr/19'and [Store No_] = ''

Upvotes: 0

Chanukya
Chanukya

Reputation: 5883

Remove Case repeated twice one expression is enough for your requirement

update [Test$ABC]
Set [Unit Price] =(
CASE WHEN [Item No_]= '12345' THEN '445'
     WHEN [Item No_]= '67890' THEN '645'
END
)
where [Item No_] in ('12345','67890')
and [Sales Start Date]='01/Apr/19'
and [Store No_]=''

Upvotes: 0

Related Questions