Swapping Row Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Swapping Row Values

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-12-15 : 04:10:09

[code]

CREATE TABLE [dbo].[tbl_contact_details] (
[contact_detail_id] [int] NOT NULL ,
[contact_party_id] [int] NOT NULL ,
[priority] [int] NULL ,
[phone] [char] (10) NULL ,
[extn] [char] (10) NULL ,
[descriptions] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_contact_details_bkup] (
[contact_detail_id] [int] NOT NULL ,
[contact_party_id] [int] NOT NULL ,
[priority] [int] NULL ,
[phone] [char] (10) NULL ,
[extn] [char] (10) NULL ,
[descriptions] [varchar] (50) NULL
) ON [PRIMARY]
GO

INSERT INTO tbl_contact_details
(
contact_detail_id,
contact_party_id,
priority,
phone,
extn,
descriptions
)

SELECT 1455,556,1,'34345665','4567','Tried to contact' UNION
SELECT 1456,557,NULL,'345535','5676','Could not contact' UNION
SELECT 1457,558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1458,559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1459,563,2,'68678','6788','Contacted office'

INSERT INTO tbl_contact_details_bkup
(
contact_detail_id,
contact_party_id,
priority,
phone,
extn,
descriptions
)

SELECT 1455,556,1,'34345665','4567','Tried to contact' UNION
SELECT 1456,557,NULL,'345535','5676','Could not contact' UNION
SELECT 1457,558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1458,559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1459,563,2,'68678','6788','Contacted office'

[/code]

tbl_contact_details_bkup is a backup table of the tbl_contact_details.

Basically I need the value of 563 and 556 swapped and priority also swapped for these values in the tbl_contact_details table

The results should look like below : (Check contact_party_id and
priority has been swaped for 563 and 556 values)

SELECT 1455,563,2,'34345665','4567','Tried to contact' UNION
SELECT 1456,557,NULL,'345535','5676','Could not contact' UNION
SELECT 1457,558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1458,559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1459,556,1,'68678','6788','Contacted office'

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-12-15 : 08:29:18

PLease somebody any idea how to swap the values

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-12-15 : 08:30:07

Can somebody show me an update statement.....

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-12-15 : 13:23:50

I have a correction to my code its all based on the emp_id we need to swap the values.Below shown are the corrected scritps.
[code]

CREATE TABLE [dbo].[tbl_contact_details] (
[contact_detail_id] [int] NOT NULL ,
[emp_id] VARCHAR(10) NOT NULL,
[contact_party_id] [int] NOT NULL ,
[priority] [int] NULL ,
[phone] [char] (10) NULL ,
[extn] [char] (10) NULL ,
[descriptions] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_contact_details_bkup] (
[contact_detail_id] [int] NOT NULL ,
[emp_id] VARCHAR(10) NOT NULL,
[contact_party_id] [int] NOT NULL ,
[priority] [int] NULL ,
[phone] [char] (10) NULL ,
[extn] [char] (10) NULL ,
[descriptions] [varchar] (50) NULL
) ON [PRIMARY]
GO

INSERT INTO tbl_contact_details
(
contact_detail_id,
emp_id,
contact_party_id,
priority,
phone,
extn,
descriptions
)

SELECT 1455,'E005',556,1,'34345665','4567','Tried to contact' UNION
SELECT 1456,'E005'557,NULL,'345535','5676','Could not contact' UNION
SELECT 1457,'E005'558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1458,'E005'559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1459,'E005'563,2,'68678','6788','Contacted office' UNION

SELECT 1555,'E007',556,1,'34345665','4567','Tried to contact' UNION
SELECT 1556,'E007'557,NULL,'345535','5676','Could not contact' UNION
SELECT 1557,'E007'558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1558,'E007'559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1559,'E007'563,NULL,'68678','6788','Contacted office'

INSERT INTO tbl_contact_details_bkup
(
contact_detail_id,
contact_party_id,
priority,
phone,
extn,
descriptions
)

SELECT 1455,'E005',556,1,'34345665','4567','Tried to contact' UNION
SELECT 1456,'E005'557,NULL,'345535','5676','Could not contact' UNION
SELECT 1457,'E005'558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1458,'E005'559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1459,'E005'563,2,'68678','6788','Contacted office' UNION

SELECT 1555,'E007',556,1,'34345665','4567','Tried to contact' UNION
SELECT 1556,'E007'557,NULL,'345535','5676','Could not contact' UNION
SELECT 1557,'E007'558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1558,'E007'559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1559,'E007'563,NULL,'68678','6788','Contacted office'

The result should be like that...Basically I need the value of 563 and 556 swapped and priority also swapped for these values in the
tbl_contact_details table

SELECT 1455,'E005',563,2,'34345665','4567','Tried to contact' UNION
SELECT 1456,'E005'557,NULL,'345535','5676','Could not contact' UNION
SELECT 1457,'E005'558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1458,'E005'559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1459,'E005'556,1,'68678','6788','Contacted office' UNION

SELECT 1555,'E007',563,NULL,'34345665','4567','Tried to contact' UNION
SELECT 1556,'E007'557,NULL,'345535','5676','Could not contact' UNION
SELECT 1557,'E007'558,NULL,'5678768','8989','Not at Home' UNION
SELECT 1558,'E007'559,NULL,'242423434','4534','Conacted Mrs' UNION
SELECT 1559,'E007'556,1,'68678','6788','Contacted office'

[\code]

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-12-15 : 14:19:12

Can somebody help me write an update statement on this kind of swapping

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-15 : 14:54:09

I don't get it .. do you want to swap the *priority* of item 1455 with the priority of item 1459 ? It doens't make sense to say you want to swap 563 with 556.

If so, then something simple like this works fine:

create procedure swapPriorities @id1 int, @id2 int
as

declare @priority_tmp int;

set @priority_tmp = (select priority from yourtable where contact_id = @i1)
update yourtable set priority = (select priority from yourtable where contact_id = @i2) where contact_id = @i1
update yourtable set priority = @priority_tmp where @contact_id = @id2

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources