 Swap values of two rows. [message #380416] |
Sun, 11 January 2009 22:01  |
shroffdarshan
Messages: 5
Registered: January 2009
|
Junior Member |
|
|
Suppose We have a table with a column as country.
We have two rows in the table.
Now we need to swap the country values among the two rows in the table.
How many minimum update queries would be required ?
Can you give me the query ?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Swap values of two rows. [message #380427 is a reply to message #380425] |
Sun, 11 January 2009 22:46  |
Frank Naude
Messages: 4596
Registered: April 1998
|
Senior Member |
|
|
Something like this should do:
UPDATE your_unnamed_table SET CountryName = 'England' WHERE CountryLangauge = 'English (US)';
UPDATE your_unnamed_table SET CountryName = 'US' WHERE CountryLangauge = 'English (UK)';
COMMIT;
|
|
|
|
|
|
|
|
| Re: Swap values of two rows. [message #380448 is a reply to message #380429] |
Sun, 11 January 2009 23:49 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you have a PK on Country ID and you want to swap the CountryName, then
UPDATE your_unnamed_table a
SET country_name = (
SELECT b.country_name
FROM your_unnamed_table b
WHERE b.country_id = decode(a.country_id, 3, 2, 3)
)
WHERE country_id in (2,3)
Note that when done on a large scale (many times), this technique will wreak havoc on indexes that include country_name; they will become badly fragmented.
Ross Leishman
[Updated on: Tue, 13 January 2009 06:20] Report message to a moderator |
|
|
|
|
|
| Re: Swap values of two rows. [message #380603 is a reply to message #380457] |
Mon, 12 January 2009 09:58 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can do it with a single update:SQL> create table test_061 (col_1 number, col_2 varchar2(10));
Table created.
SQL>
SQL> insert into test_061 values (1,'row 1');
1 row created.
SQL> insert into test_061 values (2,'row 2');
1 row created.
SQL> insert into test_061 values (3,'row 3');
1 row created.
SQL>
SQL> update test_061 set col_1 = case when col_1 = 2 then 3
2 when col_1 = 3 then 2
3 else col_1
4 end;
3 rows updated.
SQL>
SQL> select * from tesT_061;
COL_1 COL_2
---------- ----------
1 row 1
3 row 2
2 row 3 |
|
|
|
|
|
|
|
| Re: Swap values of two rows. [message #380790 is a reply to message #380712] |
Tue, 13 January 2009 06:20 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Firstly, both of these solutions will update every row in the table. Not so bad if there are a few hundred; tragic if there are a few million.
But more importantly, they are updating the key column (col_1, or country_id in my example) instead of the non-key column.
Of course, both of these issues could be easily fixed with
UPDATE your_unnamed_table a
SET country_name = DECODE(country_id, 2, 'England', 'US')
WHERE country_id in (2,3)
But then the OP did ask us to 'swap the country values', which to me implies that we use the values stored in the table, not values that we hard-code into the SQL. Hence the sub-query.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
