Swapping of Column, Finding duplicates record

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

SQL Server Development (2000)

Swapping of Column, Finding duplicates record

Author

Topic

rahul8346
Starting Member

21 Posts

Posted - 2006-04-25 : 01:49:46

1) How can i swap all the values of two columns of a table with each other, using a query alone.Can you please give an example
2) How can i find only the duplicates record of a table using a query alone.Any example would be useful.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-25 : 01:57:44

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-25 : 02:02:42

For your Question 1


Declare @tbl Table 
( Col1 Varchar (10),
Col2 Varchar(10)
)

Insert @Tbl 
Select 'A','D' Union All 
Select 'B','E' Union All 
Select 'C','F' 

Select * From @Tbl 

Update @Tbl Set Col1 = Col2 , Col2 = Col1 

Select * From @tbl

Question 2

you can find out using group by and havving clause..

post same sample data so that we can guide you in the right direction..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rahul8346
Starting Member

21 Posts

Posted - 2006-04-25 : 02:04:38

Thanks for the Help, but i am awrae of the Sql basics. I wish to know
how to do you swaps two columns values using an sql query involving Update.
Like...
Update tablename set column1= column2 and column2=column1.

I tried similar query but it didnt work...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-25 : 02:16:42

>>Update tablename set column1= column2 and column2=column1.

It means you dont know how to use Update statement

2 Select col from yourTable group by col having count(*)>1

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-25 : 02:20:34

Can you post the sample data what you were trying to swap ?? it may be possible due to different datatype etc..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rahul8346
Starting Member

21 Posts

Posted - 2006-04-25 : 02:56:59

I am having a table named tblCustomers, which has three field id(int), firstName(varchar),lastname(varchar). I wish to write an single Update query to the move the firstName data to lastname and vice versa. Is it possible ?

tblCustomers
--------------------------
id | firstName | lastname
--------------------------
1 | ram | krishnan
.
.
.
.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-25 : 03:56:03

The Sample which i posted earlier should work.... what query you are trying ???


Declare @tblCustomer Table 
(
[ID] Int,
[FirstName] Varchar (10),
[LastName] Varchar(10)
)

Insert @tblCustomer 
Select 1,'Ram','Krishnan' Union All 
Select 2,'John','Abrahim' Union All 
Select 3,'Ashwariya','Rai' 

Select * From @tblCustomer 

Update @tblCustomer Set [FirstName] = [LastName] , [LastName] = [FirstName]

Select * From @tblCustomer

EDIT: Are any triggers on the table which has some validations set for you???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rahul8346
Starting Member

21 Posts

Posted - 2006-04-25 : 05:22:59

Well i know that i can do the task using stored procedures and other ways, But I wish to know is there any "Single Update Query"or a single line query which can replace this lengthy methods used ? Ita an interview question asked to me, if only you can spare some time replying me, please do so.
Thanks.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-25 : 05:30:43

Well that was the just a sample table.. which i create .. the only script for interchanging the record is this much only ..

Update @tblCustomer Set [FirstName] = [LastName] , [LastName] = [FirstName]

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rahul8346
Starting Member

21 Posts

Posted - 2006-04-25 : 06:11:48

Thanks A lot.....thanks for the help. Regarding finding the duplicates row of the table, i will try it out what you guys have suggested. Thanxs

rahul8346
Starting Member

21 Posts

Posted - 2006-04-25 : 06:33:24

I wish to get the duplicates records of the table work.
Table structure is >> work(fname varchar(50),lname vrachar(50))

My Query:
select * from work left outer join (select distinct * from work as workable) on workable.fname=work.fname where workable.fname is null

Error: "Incorrect syntax near the keyword 'on'"

Question: Is it possible to specify an subquery in the join statement to specify the table ? Does the way i m creating the alias of the table is correct ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-25 : 06:55:05

i think the 'as workable' needs to be outside the ")"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-25 : 06:55:16

select * from work left outer join (select distinct * from work) as workable on workable.fname=work.fname where workable.fname is null

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources