I can't seem to figure this out, trying to update from another table based on a case expression: Keep getting a syntax error by the join clause.
UPDATE Table1
SET Table1.Id =
CASE WHEN Table1.AlphaCol = 'UNKNOWN' THEN 0
ELSE Table2.Id
END
JOIN Table2
ON Table1.AlphaCol = Table2.Description and Table1.Type = Table2.Type
WHERE Table1.Id is NULL
I have 1 table (Test1)
A B C D
-----------------------------
010 15.2 20 0
030 13.8 25 0
090 10.3 1.2 0
010 7.25 10 0
030 25.1 25.23 0
010 21.01 14 0
090 10.50 8 0
I want to use SP to do something like this
UPDATE Test1
If [A] = '010' then
SET [D] = [B]
end if
If [A] = '090' then
SET [D] = [C]
end if
It is possible to use SP and only one UPDATE statement ?
This is not acceptable (the table is read twice, 200.000 - 300.000 rows)
UPDATE Test1
set [D] = [B]
WHERE [A] ='010'
+
UPDATE Test1
set [D] = [C]
WHERE [A] ='090'
Okay, so i have to check to tables against each other.
if record in Table1 IS NOT in Table2 i want to set dnc to '' or null if record in Table1 IS in Table2 i want to set dnc to 'Y'
currently, i have to do two passes. i was wonder if anyone would help me make this one statement.
Thanks!
update t1
set dnc = ''
from test_new.dbo.consumer t1
go
update t1
set dnc = 'y'
from test_new.dbo.consumer t1
inner join residential.dbo.dnc on tl1=phone
go
Im trying to do the following but get the error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
The code is
update [table]
set [field 1] = (select (case [field 2]
when 'B' then 'Y'
when 'C' then 'N'
when 'N' then 'N'
when 'P' then 'Y'
else 'N'
end)
from [table]),
[field 3] = (select (case [field 2]
when 'B' then 'Y'
when 'C' then 'Y'
when 'N' then 'N'
when 'P' then 'N'
else 'N'
end)
from [table])
ANy ideas?
I am selecting from the database table and while I am selecting I am changing the value of the field
so thisis how it goes.
SELECT
.InCompleteOther = CASE WHEN (ErrMessage='Beneficiary in status') THEN 'You have End_Stage'
ELSE InCompleteOther
END
From MYTABLE
some how I am getting an error what could be it.
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.
Hi,
I have a table as follows:
CREATE TABLE [MyTable] (
[SaleYear] [smallint] ,
[SaleMonthNum] [smallint] ,
[SaleMonthName] [nvarchar] (20) ,
[TotalSale] [money] NULL
) ON [PRIMARY]
GO
What I want is to update the field 'SaleMonthName' based on the 'SaleMonthNum' field using the CASE expression. I want something like this (pseudo-code):
update Mytable
set SaleMonthName
Case:
IF SaleMonthNum =1, then := 'January'
IF SaleMonthNum =2, then := 'February'
...
IF SaleMonthNum =12, then := 'December'
Many TIA.
Hi guys,
My first question is, is it possible like this?
case
when a=b then
update table1
set columnA = convert(datetime,columnB,113)
from table2
where a=b
insert into table1 (columnames)
select (few columns, and few defined value)
from table2
where a=b
else
insert into table1 (columnames)
select (few columns)
from table2
where a=b
end
from there u can know im a newbie n know nothing bout sql :D
Thanks in advance guys!
Hi,
I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me.
How do i use case statements to update only when totalamount!=calcamt.
update c
set totalamount= calcamt
from Prepay c
JOIN
(select sum(amt) as calcamt, payid
from pay
group by payid
)b ON b.payid= c.payid
where c.cust_no='somenum'
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 RelatedHi,
I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me.
How do i use case statements to update only when totalamount!=calcamt.
update c
set totalamount= calcamt
from Prepay c
JOIN
( select sum(amt) as calcamt, payid
from pay
group by payid
)b ON b.payid= c.payid
where c.cust_no='somenum'
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
Hello everyone,
is there anyway to use an update sub query in a case expression , something like this for exampleselect username,(case when password ='606' then (update users set username = 'me' where id= '3') else password end) from users i have been googling this issue just with no usefull resultsthanks for any replyMahmoud Manasrah
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
This is on the uniqueidentifier column. Both tables are on the same database and have the same COLLATE Latin1_General_CI_AS.
But my plain update and insert results are all upper case.
Then when I tried to force by using
Insert (cols1, cols2) Values (source.col1, source.col2) COLLATE Latin1_General_CI_AS
and
Update set target.col1=source.col1, target.col2=source.col2 target.col1=source.col1 COLLATE Latin1_General_CI_AS
I got
Msg 447, Level 16, State 0, Line 32
Expression type uniqueidentifier is invalid for COLLATE clause.
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
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up.
The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser](
[AutoNumber] [int] IDENTITY(1,1) NOT NULL,
[SubmittedDate] [datetime] NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Department] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[DepartDate] [datetime] NULL
[RequestedBy] [varchar](100) NOT NULL,
[UpdatedBy] [varchar](16) NOT NULL,
[CurrentStatus] [varchar](10) NOT NULL ,
[DateCurrentStatus] [datetime] NULL,
[FormType] [varchar](10) NOT NULL,
) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing.
I want to do this for each record:
Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user
Convert Department to a 4 character department number
Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType + a.Department
CASE
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Thank you, forum participants. You are the best!
Hi, all here,
I am having a question-is it possible to update several columns using CASE expression in SQL language? like if I wanna set each CASE for each column.
Thanks a lot in advance for any help and guidance.
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'
[code]....
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 am relatively new to complex queries and need creating a query using a CASE in order to update columns to be either A or B. A few things about this is that I am joining tables from linked servers as well. This is the last part. I execute the query and receive the error:
Incorrect syntax near the keyword 'from'.
select (select FirstName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName,
(select LastName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName,
(select PersonID from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,
[Code] .....
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.
I have a table A  and lookup table B.
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 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
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
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.
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
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'.
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedHello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies View RelatedI am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob