CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Justin
Justin

Reputation: 972

SWAP Selected Rows in SQL Server

I am trying to swap the selected rows as a quick way for users to selected the opposite to what they have already selected.

For example:

Current Selection:

ID    SelectedUID
------------------
1         
2         
3         CJ
4         
5

SWAP Selection: (this is what I am trying to achieve)

ID    SelectedUID
------------------
1         CJ
2         CJ
3         
4         CJ
5         CJ

Is there a function or an easy way to do this in SQL Server?

EDIT

Would this be an appropriate Stored Procedure? I am receiving an error when I execute this.

CREATE PROCEDURE [dbo].[ProcSWAPSelections]
@FROMCLAUSE AS VARCHAR(8000),
@UPDATETABLE AS VARCHAR(50),
@GUID AS VARCHAR(3),
@WHERECLAUSE AS VARCHAR(8000)

AS 
BEGIN 
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'UPDATE ' + @UPDATETABLE + '
             SET Selected' + @GUID + '.UID = CASE WHEN Selected' + @GUID + '.UID = @GUID
                                                 THEN NULL
                                                 ELSE @GUID
                                            END
             ' +  @FROMCLAUSE + '
             ' + @WHERECLAUSE + ';';

EXEC sp_executesql @sql, N'@FROMCLAUSE VARCHAR(8000), @UPDATETABLE VARCHAR(50), @GUID VARCHAR(3), @WHERECLAUSE VARCHAR(8000)', @FROMCLAUSE,@UPDATETABLE, @GUID, @WHERECLAUSE; 

END

Upvotes: 0

Views: 77

Answers (4)

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

This function should help.

CREATE FUNCTION udf_SwapValues
(
@InputStr VARCHAR(5),
@ReplaceStr1 VARCHAR(5),
@ReplaceStr2 VARCHAR(5)
)
RETURNS VARCHAR(5)
AS
BEGIN

    DECLARE @Return VARCHAR(5)
    SELECT @Return = CASE
                 WHEN @InputStr = @ReplaceStr1 THEN @ReplaceStr2
                 WHEN @InputStr = @ReplaceStr2 THEN @ReplaceStr1
                 ELSE @InputStr
                 END

    -- Return the result of the function
    RETURN @Return

END
GO

USAGE:

DECLARE @table TABLE (ID INT,
                  SelectedUID VARCHAR(5))
INSERT INTO @table
VALUES  (1, ''),
       (2, ''),
       (3, 'CJ'),
       (4, ''),
       (5, '')

SELECT ID,SelectedUID
FROM @table

--USAGE WITH SELECT

SELECT ID,
      dbo.udf_SwapValues(SelectedUID, 'CJ', '')
FROM @table

--USAGE WITH UPDATE
UPDATE t
SET t.SelectedUID = dbo.udf_SwapValues(t.SelectedUID, 'CJ', '')
FROM @table t

SELECT *
FROM @table

Upvotes: -1

gbn
gbn

Reputation: 432662

Assuming only 2 different values in the table in one update, which means you don't need a transaction to span multiple UPDATEs

UPDATE
   SomeTable
SET
   SelectedUID = CASE WHEN SelectedUID <> 'CJ' THEN 'CJ' ELSE '' END

or...

   SelectedUID = CASE WHEN SelectedUID = 'CJ' THEN '' ELSE 'CJ' END

or...

   SelectedUID = CASE WHEN SelectedUID = 'CJ' THEN NULL ELSE 'CJ' END

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

Another way would be to have a third table

Selections
ID Selection
1  NULL
2  CJ

Then in the swap table link to it, then you only need to switch in the selections table. Same number of operations, but only two records updated.

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33581

You can do this easily with 2 updates.

Update YourTable Set SelectedUID = 'SomeValueThatDoesntExist'
where SelectedID = 'CJ'

Update YourTable Set SelectedUID = 'CJ'
where SelectedID = 'SomeValueThatDoesntExist'

Upvotes: 2

Related Questions