SQL Server 2012 :: Replacing CASE Statement In Update With Table-driven Logic
Oct 20, 2014
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.
View 9 Replies
ADVERTISEMENT
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
What is the best way to script this
View 1 Replies
View Related
Apr 9, 2014
Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'
View 1 Replies
View Related
Dec 28, 2003
I have what I thought would be a simple problem: my SQL call has a few NULLs that I want to replace with 0s. Wherever I wanted this to happen, I put in code like this:
[code]CASE colName WHEN NULL THEN 0 ELSE colName END as colName[/code]
And it doesn't work...the column is the same as it was before I put in the case statement.
What am I doing wrong? Any ideas?
Thanks a lot,
-Starwiz
View 4 Replies
View Related
Aug 9, 2015
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I have attached my data model for reference.
View 9 Replies
View Related
Aug 31, 2007
can plz anyone tell me how to fix the following update script. thanks
Update table
set rating =
case
when rating in
(select code from tbl_Codes
where code = '0')
then Rating = '0'
when Rating in
(select code from tbl_Codes
where code = '1')
then InternalRating = '1'
else rating
end
View 5 Replies
View Related
Feb 13, 2014
Aim – when Fee_Code = ‘42B’ and month_end_date =>2013-02-01 change the Fee_Code from “42B” to “42C”. Anything prior to 2013-02-01 the fee_code needs to remain the same
I can do this as a case statement(as seen below) but this creates a new column. How can i overwrite this logic in the fee_code column ?My query is
SELECT
FDMSAccountNo,
Fee_Code,
month_end_date,
sum(Fact_Fee_History.Retail_amount) as 'PCI',
Case
when
fee_code = '42B' and (month_end_date >='2013-02-01') then '42C' end as Test
from Fact_Fee_History
[code]....
View 2 Replies
View Related
Nov 15, 2013
I want to update multiple column in one table using with case statement. i need query pls..
stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL
View 2 Replies
View Related
Nov 3, 2015
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE
WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
WHEN PracticeCode IN (.......) THEN 1
WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1
ELSE 0
END
I need to put these conditions in config table and generate dynamic sql.
What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
View 2 Replies
View Related
Oct 7, 2015
I'm trying to replace a particular part of a row in a table with a new value.
The row is called "DataPath" and it has a lot of values like so:
mashOperationsComponent Data FilesSantec
I want to run a query to replace the mash with our DFS namespace share name companysharesDepartments but keep everything else past the mash part of the original row.
I'm currently running this query, it says it is altering 30,000 rows, but it doesn't look like it's doing anything at all..
UPDATE dbo.Part
SET DataPath = REPLACE(DataPath,'company.localsharesDepartments','mash')
WHERE DataPath like 'mash\%'
So for example, it would change the mash above to
company.localsharesDepartmentsOperationsComponent Data FilesSantec
View 2 Replies
View Related
May 2, 2008
I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.
CREATE PROCEDURE OfficeMove
-- Add the parameters for the stored procedure here
@UserName nvarchar(10),
@NewLocation nchar(5),
@NewCity nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Execute as user = '***'
DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
WHERE User_Name = @UserName
INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments
SET User_Name = @UserName,
Room_ID = @NewLocation
UPDATE [SQLSZD].[SZDDB].dbo.Employee_Locations
SET Office_ID =
CASE
WHEN @NewCity = 'Columbus' THEN 1
WHEN @NewCity = 'Cleveland' THEN 2
WHEN @NewCity = 'Cincinnati' THEN 4
WHEN @NewCity = 'Raleigh' THEN 5
WHEN @NewCity = 'Carrollwood' THEN 6
WHEN @NewCity = 'Orlando' THEN 7
END
WHERE User_Name = @UserName
END
GO
View 4 Replies
View Related
May 9, 2008
Hi guys
We have a table X with a gender column taking values M(male) or F(Female).There are 52 rows in the table X.We want to update all the rows with Gender M as F and update all remaining F as males (M) in a single Update Query.Im looking for the exact logic we would write in the single update statement.
Please help me out.
Thanks in advance
Regards
ARvind L
View 2 Replies
View Related
Nov 8, 2006
hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')
View 22 Replies
View Related
Aug 5, 2014
My table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
View 5 Replies
View Related
Nov 13, 2014
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
[code]....
View 9 Replies
View Related
Jan 9, 2015
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.
View 4 Replies
View Related
Feb 4, 2014
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
View 5 Replies
View Related
Aug 20, 2014
Is this possible, I am trying to execute a stored procedure depending on what parameter is selected, something like this????
Case
when field = 'value' then execute sp_procedure else execute sp_procedure_2 end
case
View 1 Replies
View Related
Aug 21, 2014
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?
View 5 Replies
View Related
Oct 6, 2014
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>
View 9 Replies
View Related
Oct 14, 2014
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
View 9 Replies
View Related
Apr 14, 2015
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.
View 3 Replies
View Related
Jun 3, 2015
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'
View 8 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Feb 14, 2014
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
View 5 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Oct 13, 1999
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.
View 2 Replies
View Related
Jul 20, 2005
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)
View 1 Replies
View Related
Jun 18, 2014
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),
[Code] ....
Expected Result:
View 5 Replies
View Related
Nov 25, 2014
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'"
View 4 Replies
View Related
Mar 12, 2015
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.
View 4 Replies
View Related
Mar 6, 2008
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
View 8 Replies
View Related
Nov 15, 2013
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..
VFP9.0 via MySQL 5.0
View 5 Replies
View Related