CODEWITHSUNDEEP
sql-servert-sql
Jobin Joseph
Jobin Joseph

Reputation: 110

How to dynamically replace a column value with another value based on condition

I am trying to dynamically change field value using a select query based on the person's previous room number.

Below are the data sample and the query I have tried. But it is throwing an error saying that there are multiple values returned by the inner query. 

      ,A.[Room],A.[CSR],A.[MemberShip],A.[NatCode]
      ,A.[MarketCode],A.[Adult],A.[Children],A.[ArrDate]
      ,A.[DepDate],A.[ResvStatus], CASE WHEN A.[Room]>9000 THEN (SELECT MIN(A.[Room])FROM [RESDETAILS] C WHERE C.GuestName=A.GuestName) ELSE A.[Room] END AS [Room]
  FROM [ITHAAFUSHI].[dbo].[RESDETAILS] A
  WHERE [GuestName]= 'Mr Jobin Joseph' 
   GROUP BY 
       A.[BusinessDate],A.[GuestName],A.[TravelAgent]
      ,A.[Room],A.[CSR],A.[MemberShip],A.[NatCode]
      ,A.[MarketCode],A.[Adult],A.[Children],A.[ArrDate]
      ,A.[DepDate],A.[ResvStatus]

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, < =,>, >= or when the subquery is used as an expression."

Raw data

Below is the expected result when Room No is >9000

Expected result

Upvotes: 0

Views: 252

Answers (1)

yarouf
yarouf

Reputation: 26

Your (SELECT MIN(A.[Room])FROM [RESDETAILS] C WHERE C.GuestName=A.GuestName) return more than 1 row so you need to use group by inside :

SELECT MIN(A.[Room])FROM [RESDETAILS] C WHERE C.GuestName=A.GuestName GROUP BY C.GuestName)

Upvotes: 1

Related Questions