SQL Server 2012 :: Update Statement With CASE Statement?
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.
Startdate = CASE WHEN Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013' Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012' Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011' Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010' Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009' Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008' END
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff @filter1 varchar(100) = '', @filter2 varchar(100) = '' AS BEGIN SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension, Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned.
Agent_ID Agent_ID2 11111 22222 <code> SELECT DISTINCT CASE WHEN [AGENT_ID] is not null then AGENT_ID when agent_id2 is not null then agent_id2 end as extension FROM [AA_Helper].[dbo].[tblEmpData] </code>
1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:
SELECT A.City_Name, B.Country_Code, B.Country_Area FROM Cities A JOIN Countries B ON B.Country_Id = A.Country_Id WHERE B.Country_Type='ABC';
That statement works absolutely fine, very fast (less than a second) and returns me 2 records
2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:
SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area FROM Cities A JOIN Countries B ON B.Country_Id = A.Country_Id WHERE B.Country_Type='ABC';
Now to get the same two records it takes 03:55 minutes (!)
I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.
It is SQL 2012 SP1 with compatibility level set to 110
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2)) Declare @S nvarchar(255) Insert into @T Values ('AR'), ('AT'), ('AW') Set @S = 'Auto Repairs' Select * from @T T where case @S when 'Auto Repairs' then 'AR' when 'Auto Target' then 'AT' when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN*** Example Record 2. Theale, Nr Reading, Berkshire Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0 THEN LEN(Address ) ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0 THEN LEN(Address) ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' + Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727)) + ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID) From Parts As P Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187 Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
Is it possible to use CASE in update statement? What is the syntax? I need to pass parameter @week, when @week = 0, 2, 4, then field = @Status and so on Thanks.
I would like to update a decimal column in a temporary table based ona set of Glcodes from another table. I search for a set of codes andthen want to sum the value for each row matching the Glcodes. Theproblem is I keep getting multiple rows returned errors."Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated."This is correct as there can be many rows matching the Glcodes foreach iteration of the case statement and I need to catch them all.I have posted some of the code below and would appreciate any help asI'm scratching my head over this one. It's all very much work inprogress again.Regards,DECLARE@CostCentreNVARCHAR(3)DECLARE@COIDNVARCHAR(3)DECLARE@TheYearNVARCHAR(5)DECLARE@PlorBSNVARCHAR(2)DECLARE@BusinessUnitNVARCHAR(50)DECLARE@BranchNVARCHAR(3)SET@CostCentre= 'xxx'SET@COID= 'inc'SET@TheYear= '2004'SET@PlorBS= 'x2'SET@BusinessUnit= 'PBUS'SET@Branch= ‘usa'CREATE TABLE #SummaryTempTable ([GLD_ACCTNG_PER] int,[Order Num] decimal(9,2),[Summary Description] varchar(50),[Summary Amount] decimal(9,2))INSERT INTO #SummaryTempTable VALUES(199999, 1.1, 'Tot Ext Sales',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 1.2, 'Tot Int Sales',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 1.3, 'Inter Mark Up',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 2.1, 'Tot Ext Costs',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 2.2, 'Tot Int Costs',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 2.3, 'Inter Mark UpCharges', 0.0)UPDATE #SummaryTempTableSET [Summary Amount] = (SELECT sum(CASEWHEN ((ACT_GL_NO between '4000' and '4059') or (ACT_GL_NO between'4065' and '4999') or (ACT_GL_NO IN ('4062','4063'))) THEN GLD_TotalWHEN (ACT_GL_NO IN ('4060','4064')) THEN GLD_TotalWHEN (ACT_GL_NO = '4061') THEN GLD_TotalWHEN ((ACT_GL_NO between '5000' and '5059') or (ACT_GL_NO between'5065' and '5401') or (ACT_GL_NO IN ('5805','5806','5062','5063')))THEN GLD_TotalWHEN (ACT_GL_NO IN ('5060','5064')) THEN GLD_TotalWHEN (ACT_GL_NO = '5061') THEN GLD_TotalELSE 0END)FROM howco_dw_test.dbo.cubeFinancePeriodWHERE ([coid] = @COID) AND (GLD_SSN_BRH = @Branch) AND(GLD_ACCTNG_PER like @TheYear) AND ACT_GL_NO BETWEEN 4000 AND 9999AND GLD_CST_CTR IN ('008','021','031','041')GROUP BY ACT_GL_NO, GLD_ACCTNG_PER)
Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do i have a table like this Field1 Field2 Field3 FieldType1 FieldType2 FieldType3 1-when there is no data for a given combination of Field2 and Field3,i need to do "insert".i.e the very first time and only time 2-Second time,when there is already a row created for a given combination of Field2 and Field3,i would do the update onwards from there on. At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure CREATE PROCEDURE dbo.StoredProcedure ( @intField1 int, @intField2 int, @intField3 int, @intFieldValue int , @evalFieldName varchar(4) )So i am trying something like CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3) CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3) CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue) END similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing stored procedure to insert/update for each individual fields..thanks a lot
Update ed_abcdeeh set category = case when name_of_school = '' then category = 'No Facility' else '' end,status = case when name_of_school = '' then status = 'Non-Compliant' else 'Compliant' end.
How to make this query right.. when name of school is blank i want to update my category to No facility, but if the name of school has data it will just make it blank. same to the status..
I am trying to run the below but I get an error of 'Incorrect syntax ')'' Â --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.
UPDATE abcdefg SET [Date] = GETDate(), [readytogo] = ( CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view]) THEN 'Yes' ELSE 'Needs Verification'
SELECT SUM(((CASE WHEN o.date>= a.activity_date, other filter condition, other filter condition THEN (select coalesce(d.balance,d2.balance) from drawtable d where coalesce(d.date, d2.date) < a.activity_date order by d.date desc limit 1) - ( select coalesce(d.balance, d2.balance) from drawtable d where coalesce(d.date, d2.date) = interval 'current date' else end ))
from emailtable a LEFT JOIN opportunity o left join drawtable d left join drawtable d2 etc
The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement?
I have successfully built a messaging system into my application, I now in the process of displaying the messages in the UI.
The following are how my tables are constructed.
CREATE TABLE [MailBox].[Message]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [SenderId] [bigint] NOT NULL, [Message] [nvarchar](max) NOT NULL, [SentDate] [datetime] NOT NULL, CONSTRAINT [PK_MailBox.Message] PRIMARY KEY CLUSTERED
[Code] .....
Now I haven't set the foreign key on the MessageReceipient table yet. When someone sends me an email I insert a record into [MailBox].[Message] and output the insert id into MessageReceipient along with the ReceipientId this is working as expected, when I then click on my inbox I call the following stored procedure:
Select p.Username, count(mr.RecipientId) [TotalMessages], CASE WHEN mr.ReadDate is null then 1 -- New message WHEN mr.ReadDate is not null then 0 -- Message has been read END AS NewMessage FROM [User].[User_Profile] p JOIN [MailBox].[Message] m on p.Id = m.SenderId JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId GROUP BY p.Username, mr.RecipientId, mr.ReadDate
This will give me the person who has emailed me, the total amount of messages and if the message is new or its been read, I do this by checking the ReadDate column as shown in the case statement (but this gives me duplicates, which is not what I want). Lets say user1 emails me 5 times so when I call this proc I will have the same user displayed to me 5 times, what I'm trying to achieve with the proc is it will show User1 as the following:
User1 5 Messages 1 or 0 New Messages
I can get it to display as follow when I remove the case statement
User1 5 Messages
but as soon as I add the case statement back in then I get 5 rows.
How can I change this proc in such a way that it will display the data as follows;
User1 5 Messages 1 or 0 New Messages
New Messages is dependent on ReadDate if its null then we have a new message, otherwise its been read.
I'm trying to create a case statement that if a field = a certain code, I'd like to take another field * 0.9. But, I'm getting a Null value for the answer..here is the statement:,case when parts.ndc = '50242-0138-01' then labels.BAGSDISP*0.9 end "Units Dispensed"..For this example labels.BAGSDISP is a value of 2. So, in theory it should be 2 * 0.9 and the result should be 1.8 but I'm getting a NULL
I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.I am trying to write a case statement like so:
CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A' THEN 'ADJ' WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R' THEN 'REV' WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR' THEN 'REV' WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR' THEN 'CREDIT' ELSE NULL END AS 'Status'
For the most part the code is working, with the exception of the fields that just end in 'R'. An example of this is 471268R, 2525125901CRR, 11100325230CR Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal). How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?
I am unable to update the data using record by record below scenario.
Required output:
patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.
Current Output:
Calculation: From index_admission discharge date to next admit_visit date,
1) if the diff is having less than 30 days, readmission=1 and Index=0
else readmission=0 and Index=1 should be update.
For checking this every time should check using the latest index_admission discharge_date.
To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.
UPDATE Readmission SET Index_AMI = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM (SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub) , FPR.Admit_Date_Time) between 0 and 31 THEN 0 ELSE 1 END),
I have a simple update statement (see example below) that when runs, I expect to see the number of records updated in the Results tab. This information shows up in the Messages tab; however, what is displayed in the Results tab is (No column name) 40. From where the 40 is being generated. I have tried restarting SSMS 2012, restarting my computer, turning NOCOUNT on and off.
"UPDATE TableA SET Supervisor = 'A123' WHERE PersonnelNumber = 'B456'"
I have created a Dynamic Merge statement SCD2 Store procedure , which insert the records if no matches and if bbxkey matches from source table to destination table thne it updates old record as lateteverion 0 and insert new record with latest version 1.
I am getting below error when I ahve more than 1 bbxkey in my source table. How can I ignore this.
BBXkey is nothing but I am deriving by combining 2 columns.
Msg 8672, Level 16, State 1, Line 6
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.  Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume)  WHEN c.TYPE = 'DEF' THEN (c.volume)  WHEN c.Type = 'GHI' THEN (c.volume)  Else 0  END),  CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'  WHEN c.Type = 'ABC' THEN '123.1'  WHEN c.Type = 'DEF' THEN '234.2' WHEN c.Type = 'GHI' THEN '567.1' END  from table1 a join table2 b  on a.Cust = b.Customer  join table3 c  on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
table A: | ID | FRUIT | VEGETABLE | GOOD | -------------------------------------------- |  1 | orange | cabbage    |  no  | |  1 | apple | lettuce       | yes  | |  1 | kiwi    | broccoli     |  no  | |  1 | pear   | kale          | yes  |
table B: | ID | FRUIT | VEGETABLE | ------------------------------- | 1 | apple | lettuce      | | 2 | pear   |  kale        |
If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.
This is what I have so far.
update tableA set GOOD = (case when tableA.id = C.id then 'yes' else 'no' end ) from (select tableA.id as id from tableA A left join tableB B on B.fruit = A.fruit and B.vegetable = A.vegetable) C
I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this
case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO
