How To - swap data between two row
-
hi all,
While running a sql query, if i want to interchange the data of two rows. how can i do it?
e.g. suppose there are two table : T - A, T - B
I am running a sql query which includes both T - A and T - B.
There are two one column in both the tables with some data.
T - A column EMP ID
T - B column DEPT ID.
Now if i want to change the data of T - A to T - B and T - B to T - A.
How can i do it?
Thanks ,
Prajakta
-
Add a column to A
Copy B data to A's new Column
Copy A data to B's column
Copy new A column to old A column
Drop A's new column
-
I'm thinking there better be a reference in the two tables that directly relates the rows, as well...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Who needs that crap... wasn't it you that proposed a fully xmled Entity-value system? No need for all those table things anymore
. -
Now, THAT's funny!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Smily,
They're making fun of you (in the good sense of that concept) because your problem sounds like you're from outer space. You need to come down to earth and describe your problem better to us simple folks.
-
I'm not laughing at anyone... except at one comment Jeff made a few days (or weeks) ago.
-
It was a funny comment, indeed.
No, we're not make fun of anyone except for each other
But, I was serious about needing to make a reference to the primary key... otherwise, there's no way to identify which employee row goes with which department row for the swap.
Me? I'd load a temp table with the primary key (whatever it is) and the employee id from the employee table along with a blank department id column. Then, I'd update that column, again, based on the primary key. Then, it's a simple matter of updating employee with department and vise versa.
Gotta ask... why on Earth would you want to do this? Perhaps a cleanup script to cleanup the data after maybe a bit of bad GUI code?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Still waiting here on Earth... why would you want to swap data between two obviously disparate tables?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hi all i was searching on google and found this thread...
i have an integer Sequence column in a table, i have a feature in the front end to swap the sequence between 2 rows. is there a single T-SQL command to do that?
-
UPDATE dbo.YourTable SET Seq = CASE Seq WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE Seq END WHERE Seq IN (1, 2)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply