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 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'.
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)
View 1 Replies View RelatedIs 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..
VFP9.0 via MySQL 5.0
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 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
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies View RelatedI 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.Â
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.
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'
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
I compare two table with JOIN statement. Now I would like to update one of them base on result. How it to do?
View 2 Replies View RelatedHello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View 8 Replies View RelatedI need to figure out the correct update statement syntax for the following integration.
I have a "Performance Table" which i insert weekly performance numbers into for each store. The table is constructed w/ columns such as Store, Weekenddate, Sales, Refunds, #ofPatients
In a "Averages Table" i have every weekenddate for each store populated. So 52 Weeks for 10 stores = 520 Rows of Store numbers & WeekendDates.
What i would like to do is run a loop or update statement which would update the store average for each weekendate based on the last 13 weeks.
This is my query
update performancestore_avgs set SalesAvg =
(select sum(SalesHit)/Count(Store) from performance_store where performance_store.weekenddate >= performancestore_avgs.weekenddate-84 and performancestore_Avgs.store = performance_store.store)
The update statement runs but the averages are completely wrong.
I want to update records in 1 table with the result of a select statement.
The table is called 'MPR_Portfolio_Transactions' and contains the following fields:
[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]
And the select statement is like this:
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
the key is PTR_SEQUENCE
I am cleaning up a large database table that has Date keys instead of real DateTimes. To do this, I am running the following query...
UPDATE MQIC.DBO.OBSERVATION_F
SET MQIC.DBO.OBSERVATION_F.OBS_DATE = MQIC.DBO.DATE_D.ACTUAL_DATE
FROM MQIC.DBO.OBSERVATION_F INNER JOIN MQIC.DBO.DATE_D
ON MQIC.DBO.OBSERVATION_F.DATE_KEY = MQIC.DBO.DATE_D.DATE_KEY
where Actual_Date is what is being stored, and the Date_Key is to be dropped.
The particulars are this -
Date_D table - 92,000 rows - 40 MB
Observation_F - 2,000,000 rows - 3.2 GB
This is being run on a remotedly hosted rack server with an AMD processor, 1 GB RAM, 60 GB harddisk space, 20 GB used.
SQL-Server 2005 Express - SP1
If I do the same query as a SELECT Statement,
UPDATE MQIC.DBO.OBSERVATION_F
SELECT MQIC.DBO.OBSERVATION_F.DATE_KEY, MQIC.DBO.DATE_D.ACTUAL_DATE
FROM MQIC.DBO.OBSERVATION_F INNER JOIN MQIC.DBO.DATE_D
ON MQIC.DBO.OBSERVATION_F.DATE_KEY = MQIC.DBO.DATE_D.DATE_KEY
it runs to completion in about 15 min - during the entire time there is extensive used of CPU from Task Manager.
If I do the above statement, it seems to use lots of resources (50% +) for about 5 min, then falls to 5%. It just seems to sit there, for an hour + at which time I've killed the query.
This is actually the second time I tried this. The first time was on a different machine, with the P4, 3GB RAM, plenty of disk space, and using SQL-Server 2005 Standard - SP1. Exactly the same decrease in resources happened, and even though it ran several hours, no results.
Any thoughts here - not waiting long enough, memory leaks, etc.?
Thanks!
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
Suppose your using Merge Replication.
2 Users in 2 locations issue updates to the same table. 1 updating 1 column and the other updating another column. Now in reality the actual Stored Procedure issuing the update statement is passed in all the possible columns that could change and builds an update statement that updates all columns even the ones that havent changed.
Will this break Merge Replications conflict tracking? Or does SQL Server 2005 Merge Replication pickup that in reality the 2 updates only in reality changed the values in 2 columns.
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows
as per data in MS Sql
my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --
2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no
3)Other_project
fields are -- other_proj_id,other_custname,po
for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20
Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222
material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output
plz send a sql query
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray
View 1 Replies View RelatedI am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedOk 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.
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!
View 7 Replies View RelatedHi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.
View 9 Replies View Related