how to Swap records
-
Hi,
I have 5 column table , out of which first 3 columns are member of composite primary key.
I need the swap the value of the column 3 whith other row how do i do this .
Thankx
Gopi
-
quote:
I need the swap the value of the column 3 whith other row how do i do this .I'm not sure what you mean. I get the first part, about having a composite key. What values do you need to replace? Can you give an example?
-
If I've understood well you want to change the order of the columns in your index.
To do this first drop the index (alter table drop constraint...) and recreate it with the columns you want to see
Bye
Gabor
Bye
Gabor -
Apologize for not being clear.
Here is a sample .
Column 1=CAR Manufacturer
column 2=CAR Model
Column 3=CAR No
Column 4=Details of the CAR
column 5= ..
first 3 columns are member of composite primary key.
My requirment is to copy (SWAP)the information of one car to another car. ie
Column 4 and 5 data needs to be copied to an another car and that car's 4 and 5 column values needs to be copied to car 1.
-
How about something like the following? For simplicity, I used integer keys and left out error checking in the stored procedure.
DROP TABLE cars
DROP PROCEDURE pSwapCarDetails
GO
CREATE TABLE cars
(
CARManuf int NOT NULL,
CARModel int NOT NULL,
CARNo int NOT NULL,
Detail1 varchar(50),
Detail2 varchar(50),
PRIMARY KEY CLUSTERED ( CARManuf, CARModel, CARNo )
)
INSERT cars (CARManuf, CARModel, CARNo, Detail1, Detail2)
VALUES (1,1,1, 'Detail 1 for Car 1', 'Detail 2 for Car 1')
INSERT cars (CARManuf, CARModel, CARNo, Detail1, Detail2)
VALUES (1,2,1, 'Detail 1 for Car 2', 'Detail 2 for Car 2')
INSERT cars (CARManuf, CARModel, CARNo, Detail1, Detail2)
VALUES (2,1,1, 'Detail 1 for Car 3', 'Detail 2 for Car 3')
INSERT cars (CARManuf, CARModel, CARNo, Detail1, Detail2)
VALUES (2,2,1, 'Detail 1 for Car 4', 'Detail 2 for Car 4')
GO
CREATE PROCEDURE pSwapCarDetails
(
@CARManuf1 int , @CARModel1 int , @CARNo1 int ,
@CARManuf2 int , @CARModel2 int , @CARNo2 int
)
AS
-- NOTE: no error checking is performed
-----------------------------------------------------------------------
-- swap details for car 1 and car 3 : A -> temp , B -> A , temp -> B
-----------------------------------------------------------------------
-- save first row to temp location
SELECT CARManuf, CARModel, CARNo, Detail1, Detail2
INTO #temp
FROM cars
WHERE CARManuf = @CARManuf1 AND CARModel = @CARModel1 AND CARNo = @CARNo1
-- replace first row with details from second row
UPDATE cars
SET Detail1 = (SELECT c2.Detail1
FROM cars c2
WHERE c2.CARManuf = @CARManuf2 AND c2.CARModel = @CARModel2 AND c2.CARNo = @CARNo2) ,
Detail2 = (SELECT c2.Detail2
FROM cars c2
WHERE c2.CARManuf = @CARManuf2 AND c2.CARModel = @CARModel2 AND c2.CARNo = @CARNo2)
WHERE CARManuf = @CARManuf1 AND CARModel = @CARModel1 AND CARNo = @CARNo1
-- replace second row with saved temp values
UPDATE cars
SET Detail1 = (SELECT t.Detail1 FROM #temp t),
Detail2 = (SELECT t.Detail2 FROM #temp t)
WHERE CARManuf = @CARManuf2 AND CARModel = @CARModel2 AND CARNo = @CARNo2
-- discard temp values
DROP TABLE #temp
GO
-------------------------------------------------------------------
--
-- TEST
--
-------------------------------------------------------------------
-- display inital data
SELECT * FROM cars
EXEC pSwapCarDetails 1,1,1, 2,1,1
-- display final results
SELECT * FROM cars
-
How about:
CREATE TABLE CARS( Manufact int, Model int, Nbr int, Descr varchar(100), Other Varchar(100))
GO
INSERT INTO CARS(Manufact, Model, Nbr, Descr, Other) VALUES(1,1,1,'DESCr1','Other1')
INSERT INTO CARS(Manufact, Model, Nbr, Descr, Other) VALUES(2,2,2,'DESCr2','Other2')
Select * from CARS
Update CARS
SET Descr = Case When Manufact = 1 And Model = 1 and Nbr = 1 then C2Descr else C1Descr end,
Other = Case When Manufact = 1 And Model = 1 and Nbr = 1 then C2Other else C1Other end
FROM
(SElECT C1.Descr as C1Descr, C1.Other as C1Other, C2.Descr as C2Descr, C2.Other as C2Other
FROM CARS C1 , CARS C2
WHERE
C1.Manufact = 1 And C1.Model = 1 and C1.Nbr = 1
and
C2.Manufact = 2 And C2.Model = 2 and C2.Nbr = 2) Q
WHERE
(Manufact = 1 And Model = 1 and Nbr = 1)
OR
(Manufact = 2 And Model = 2 and Nbr = 2)
* Noel -
declare @Column4 varchar(50), @Column5 varchar(50)
select @Column4 = Column4,
@Column5 = Column5
from MyTable
where id = 1
Update MyTable
set Column4 = M2.Column4,
Column5 = M2.Column5
from MyTable M1, MyTable M2
where M1.id = 1
and M2.id = 2
Update MyTable
set Column4 = @Column4,
Column5 = @Column4
where id = 1
Bye
Gabor
Bye
Gabor -
quote:
declare @Column4 varchar(50), @Column5 varchar(50)select @Column4 = Column4,
@Column5 = Column5
from MyTable
where id = 1
Update MyTable
set Column4 = M2.Column4,
Column5 = M2.Column5
from MyTable M1, MyTable M2
where M1.id = 1
and M2.id = 2
Update MyTable
set Column4 = @Column4,
Column5 = @Column4
where id = 1
Bye
Gabor
If you divide the query into two updates you should use TRANSACTIONS to wrap them because a client could read between the two an get the wrong
dataHTH
* Noel -
You are absolutly right.
But you know here we are trying to show the way and the howtos to solve some specific problems.
Anyway you have brought a valid point, a transaction should be used.
Bye
Gabor
Bye
Gabor -
A little bit more generic solution would be to build a (temporary) table that holds for each value to replace a pointer to the new record.
CREATE table #tempTable
(CarMan int, CarModel int, CarNo int,
NewMan int, NewModel int, NewNo int)
-- put values from car 2 into car 1
INSERT INTO #tempTable VALUES (1, 1, 1, 2, 2, 2)
-- put values from car 1 into car 2
INSERT INTO #tempTable VALUES (2, 2, 2, 1, 1, 1)
-- Do the update
UPDATE Cars
SET Cars.Details1 = C2.Details1,
Cars.Details2 = C2.Details2
FROM Cars C1 INNER JOIN #tempTable
ON Cars.<Key> = #tempTable.Car<Key>
INNER JOIN Cars C2
ON #tempTable.New<Key> = C2.<Key>
dataViewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply