Hi,
pls help me here 

table t1 has 3 fields, and data is as below

name , grade , marks

n1        a          10
n2        b           20
n3        c           30
n4        a           30
n5        c           10
n6        n7         40

now i want an update query where 2 names should be swapped.

like n1 is updated as n2, and n2 updated as n1.

i tried to put update query, but problem is 

if n1= n2  then we have 2 n2's  in the table then again n2=n1 tried
then we have 2 n1's , 

i want both names should be interchanged, 

any help

STEP 1:  BACKUP your data before attempting the following.
STEP 2:  BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you 
expect.

You can use three separate queries to do this.

Update N1 to N_TEMP
Then Update N2 to N1
Then Update N_Temp to N2

You could do it in one query by using an expression in the update
UPDATE [TheTable]
SET [Name] = IIF([Name]="N2","N1",IIF([Name]="N1","N2",[Name]))
WHERE [Name] in ("N1","N2")

If you can't do this in the SQL window, post back for directions on how to 
build the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

naveen prasad wrote:
> Hi,
> pls help me here 
> 
> table t1 has 3 fields, and data is as below
> 
> name , grade , marks
> 
> n1        a          10
> n2        b           20
> n3        c           30
> n4        a           30
> n5        c           10
> n6        n7         40
> 
> now i want an update query where 2 names should be swapped.
> 
> like n1 is updated as n2, and n2 updated as n1.
> 
> i tried to put update query, but problem is 
> 
> if n1= n2  then we have 2 n2's  in the table then again n2=n1 tried
> then we have 2 n1's , 
> 
> i want both names should be interchanged, 
> 
> any help
> 
> 
> 

wow, it really worked , thanks a million

"John Spencer" wrote:

> STEP 1:  BACKUP your data before attempting the following.
> STEP 2:  BACKUP your data before attempting the following.
> 
> Without a backup you cannot restore the data if this does not work the way you 
> expect.
> 
> You can use three separate queries to do this.
> 
> Update N1 to N_TEMP
> Then Update N2 to N1
> Then Update N_Temp to N2
> 
> You could do it in one query by using an expression in the update
> UPDATE [TheTable]
> SET [Name] = IIF([Name]="N2","N1",IIF([Name]="N1","N2",[Name]))
> WHERE [Name] in ("N1","N2")
> 
> If you can't do this in the SQL window, post back for directions on how to 
> build the query.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> naveen prasad wrote:
> > Hi,
> > pls help me here 
> > 
> > table t1 has 3 fields, and data is as below
> > 
> > name , grade , marks
> > 
> > n1        a          10
> > n2        b           20
> > n3        c           30
> > n4        a           30
> > n5        c           10
> > n6        n7         40
> > 
> > now i want an update query where 2 names should be swapped.
> > 
> > like n1 is updated as n2, and n2 updated as n1.
> > 
> > i tried to put update query, but problem is 
> > 
> > if n1= n2  then we have 2 n2's  in the table then again n2=n1 tried
> > then we have 2 n1's , 
> > 
> > i want both names should be interchanged, 
> > 
> > any help
> > 
> > 
> > 
> .
>