Click here to Skip to main content
15,074,377 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hello, this is vikash.

Say I have a table with data as follows :

EmpID Grade
1 A
2 B
3 B
4 A
5 B
6 A
7 A
8 A

now i want to make all A as B and all B as A in a single update query.

How can this be done.

Please help.

Thanks in Advance.
Posted

This can be done using case block. Refer to this example:

SQL
UPDATE titles
       SET price =
                 CASE
                   WHEN (price < 5.0 AND ytd_sales > 999.99)
                                   THEN price * 1.25
                   WHEN (price < 5.0 AND ytd_sales < 1000.00)
                                   THEN price * 1.15
                   WHEN (price > 4.99 AND ytd_sales > 999.99)
                                   THEN price * 1.2
                   ELSE price
                 END



your case is much simpler.
   
Use a CASE statement (and possibly a WHERE clause to filter for only a's and b's).

SQL
UPDATE [grades]
   SET [grade] = case when [grade] = 'A' then 'B'
                    else 'A'
                    end


Cheers.
   
Comments
Uday P.Singh 29-Jun-11 15:16pm
   
good answer my 5 :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900