Help required in Update query

[naveen prasad]

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

 

[John Spencer]

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]

wow, it really worked , thanks a million

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

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

.