Home » SQL & PL/SQL » SQL & PL/SQL » Swap values of two rows. (Oracle 9i)
icon3.gif  Swap values of two rows. [message #380416] Sun, 11 January 2009 22:01 Go to next message
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 #380422 is a reply to message #380416] Sun, 11 January 2009 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can you give me the query ?
This request is nonsensical.
Rows in a table have NO inherent order.

Rows in a table are like balls in a basket.
Which ball in the basket is the "first" ball?
Re: Swap values of two rows. [message #380423 is a reply to message #380416] Sun, 11 January 2009 22:27 Go to previous messageGo to next message
shroffdarshan
Messages: 5
Registered: January 2009
Junior Member
The table does not have country as the only column. It may have other columns also. Out of that i want to swap the values of two rows for only the country column. Does it make sense now.
Re: Swap values of two rows. [message #380424 is a reply to message #380416] Sun, 11 January 2009 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.

You'll need 2 UPDATE statements & 1 COMMIT.
Re: Swap values of two rows. [message #380425 is a reply to message #380416] Sun, 11 January 2009 22:38 Go to previous messageGo to next message
shroffdarshan
Messages: 5
Registered: January 2009
Junior Member
Sorry for the inconvenience caused. Sad

Table has 3 columns -> CountryId, CountryName, CountryLangauge

DML Data
CountryId CountryName CountryLangauge
1 India Hindi
2 US English (US)
3 England English (UK)

Expected Output
CountryId CountryName CountryLangauge
1 India Hindi
2 England English (US)
3 US English (UK)

I would like to know the minimum number of update queries required for this operation. And also please provide the query.
Re: Swap values of two rows. [message #380427 is a reply to message #380425] Sun, 11 January 2009 22:46 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 #380428 is a reply to message #380416] Sun, 11 January 2009 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>We have two rows in the table.
What does previous post show THREE rows above?

It appears you do not understand what are DDL & DML statements.

What is business rule to swap rows 2 & 3?
Why not 1 & 3 or 1 & 2?

Does a Primary Key exist on COUNTRYID?
Re: Swap values of two rows. [message #380429 is a reply to message #380416] Sun, 11 January 2009 22:49 Go to previous messageGo to next message
shroffdarshan
Messages: 5
Registered: January 2009
Junior Member
A primary key exists on CountryId. Table has 3 rows.
Re: Swap values of two rows. [message #380448 is a reply to message #380429] Sun, 11 January 2009 23:49 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #380457 is a reply to message #380416] Mon, 12 January 2009 00:07 Go to previous messageGo to next message
shroffdarshan
Messages: 5
Registered: January 2009
Junior Member
Thanks. This is exactly the query i was looking for.
Re: Swap values of two rows. [message #380603 is a reply to message #380457] Mon, 12 January 2009 09:58 Go to previous messageGo to next message
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 #380696 is a reply to message #380603] Tue, 13 January 2009 01:02 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
This looks for me like simple:
update test_061 set col_1 = decode(col_1,2,3,3,2,col_1)
Re: Swap values of two rows. [message #380712 is a reply to message #380696] Tue, 13 January 2009 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what does this add to JRowbottom's answer.
Is it just you want to show how to do it with DECODE instead of CASE?

Regards
Michel
Re: Swap values of two rows. [message #380790 is a reply to message #380712] Tue, 13 January 2009 06:20 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Single SQL query to join 3 tables
Next Topic: SQL Query
Goto Forum:
  


Current Time: Fri Dec 09 00:17:49 CST 2016

Total time taken to generate the page: 0.11513 seconds