CODEWITHSUNDEEP
c#asp.netsql
user1716420
user1716420

Reputation:

How to update selective rows in a table in sql server?

I am working in an application in which user selects some rows in a gridview by clicking on check boxes associated with each row and in selected rows only one value should be updated in database. I am using following query

 update Items
 set bitIsTab = 1
 where ReqID = 3

Suppose that user selects 4 values from gridview and i have to set these 4 rows bitIsTab to 1.How to update these rows by calling query one time or i have to call the same query as many times as the number of records selected.

Upvotes: 2

Views: 242

Answers (4)

user1716420
user1716420

Reputation:

A simple solution is 

 DECLARE @id varchar(30)
 SET @id = '1,3,2'

 UPDATE [Items]
 SET bitIsTab = 0
 WHERE ReqID IN (
                    SELECT s
                    FROM dbo.Split(',', @id)
                )

where

 CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
 RETURNS table
 AS
 RETURN (
 WITH Pieces(pn, start, stop) AS (
 SELECT 1, 1, CHARINDEX(@sep, @s)
 UNION ALL
 SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
 FROM Pieces
 WHERE stop > 0
 )
 SELECT pn,
 SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
 FROM Pieces
 )

Upvotes: 1

Francis P
Francis P

Reputation: 13655

Okay the query should look like this, to update items 1,2,3,4:

 UPDATE Items
 SET bitIsTab = 1
 WHERE ReqID IN (1,2,3,4);

It can however be done using Linq:

List<int> selectedIds = { 1, 2, 3, 4 };
var itemsToBeUpdated = (from i in yourContext.Items 
                        where selectedIds.Contains(i.ReqID)
                        select i);
itemsToBeUpdated.ForEach(i=>i.bitIsTab = 1);
yourContext.SubmitChanges();

Or you could use a VARCHAR in your stored procedure:

CREATE PROCEDURE sp_setTabItems
    @ids varchar(500) AS
 UPDATE Items
 SET bitIsTab = 1
 WHERE charindex(',' + ReqID + ',', ',' + @ids + ',') > 0;

And then use "1,2,3,4" as your stored procedure parameter.

To execute the stored procedure:

 EXEC sp_setTabItems '1,2,3,4'

Could also be done in a more reusable way, with the bitIsTab as a parameter:

CREATE PROCEDURE sp_setTabItems
    @isTab bit,
    @ids varchar(500) AS
 UPDATE Items
 SET bitIsTab = @isTab 
 WHERE charindex(',' + ReqID + ',', ',' + @ids + ',') > 0;

And executed this way:

EXEC sp_setTabItems '1,2,3,4',1

I updated the stored procedure solution, since comparing a INT with a VARCHAR won't work with the EXEC.

Upvotes: 5

codingbiz
codingbiz

Reputation: 26386

If you have to pass the data as list of separated Ids, then you'll need to write a function for that how to split and insert CSV data into a new table in single statement?

 CREATE PROCEDURE sp_setTabItems
    @ids varchar(500) -- 1,2,4,6
 AS
 UPDATE Items
 SET bitIsTab = 1
 WHERE ReqID IN (Select Part From dbo.inline_split_me(@ids)

Here is the function

CREATE FUNCTION inline_split_me (@SplitOn char(1),@String varchar(7998))
RETURNS TABLE AS
    RETURN (WITH SplitSting AS
       (SELECT
            LEFT(@String,CHARINDEX(@SplitOn,@String)-1) AS Part
                ,RIGHT(@String,LEN(@String)-CHARINDEX(@SplitOn,@String)) AS Remainder
            WHERE @String IS NOT NULL AND CHARINDEX(@SplitOn,@String)>0
        UNION ALL
        SELECT
            LEFT(Remainder,CHARINDEX(@SplitOn,Remainder)-1)
                ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(@SplitOn,Remainder))
            FROM SplitSting
            WHERE Remainder IS NOT NULL AND CHARINDEX(@SplitOn,Remainder)>0
        UNION ALL
        SELECT
            Remainder,null
            FROM SplitSting
            WHERE Remainder IS NOT NULL AND CHARINDEX(@SplitOn,Remainder)=0
       )
       SELECT Part FROM SplitSting
   )

Upvotes: 0

juergen d
juergen d

Reputation: 204766

If you know the ReqIDs you can do

update Items
set bitIsTab = 1
where ReqID in (1,2,3,4)

Upvotes: 7

Related Questions