Home » SQL & PL/SQL » SQL & PL/SQL » sql problem (Oracle 9.2.0.3)
sql problem [message #436874] Tue, 29 December 2009 05:24 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
create table test110( a number, b number);
insert into test110 values('1',null);
insert into test110 values('2',null);
insert into test110 values('3',null);
insert into test110 values('1',100);
insert into test110 values('2',200);


I want to delete the rows from above table and want to keep the rows in table test110 like this below
1 100
2 200
3 null

How can I acheive this?
I tried using self join but not getting the expected result

[Updated on: Tue, 29 December 2009 05:30]

Report message to a moderator

Re: sql problem [message #436876 is a reply to message #436874] Tue, 29 December 2009 05:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
I tried using self join but not getting the expected result
What is that sql?
Is your intention to delete the rows with null, if there is another row with not null?

By
Vamsi
Re: sql problem [message #436877 is a reply to message #436876] Tue, 29 December 2009 05:41 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Vamsi,
I want a result like this in the table
1 100
2 200
3 null



Actually what happends is that I initially in the table we have

1 null
2 null
3 null

and after that the table is inserted with the below rows
1 100
2 200


I want to keep non matching one with null and the matching one with not null

I want a result like this in the table
1 100
2 200
3 null

[Updated on: Tue, 29 December 2009 05:44]

Report message to a moderator

Re: sql problem [message #436878 is a reply to message #436877] Tue, 29 December 2009 05:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I want the sql, which you have tried.

By
Vamsi

[Updated on: Tue, 29 December 2009 05:45]

Report message to a moderator

Re: sql problem [message #436879 is a reply to message #436878] Tue, 29 December 2009 05:45 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

select * from test110 a where  a.b is not null
        and  exists ( select 1 from test110 b where a.a = b.a and b.b is not null)

I am getting


1 100
2 200

But I want result like

1 100
2 200
3 null

[Updated on: Tue, 29 December 2009 05:46]

Report message to a moderator

Re: sql problem [message #436880 is a reply to message #436879] Tue, 29 December 2009 05:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I though you want to delete some rows.
delete from test110 t1
where t1.b is null
and exists (select 1
            from test110 t2
            where t1.a = t2.a
            and t2.b is not null);
And
select * from test110;

(Or) If you don't want to delete, change your select from "and exists" to "or not exists".

Moreover, you are inserting '1' to a number column.
Don't let Oracle to do implicit conversion. Pass the proper value.

By
Vamsi

[Updated on: Tue, 29 December 2009 05:52]

Report message to a moderator

Re: sql problem [message #436884 is a reply to message #436877] Tue, 29 December 2009 06:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
prachij593 wrote on Tue, 29 December 2009 12:41
Actually what happends is that I initially in the table we have

1 null
2 null
3 null

and after that the table is inserted with the below rows
1 100
2 200


I want to keep non matching one with null and the matching one with not null

I want a result like this in the table
1 100
2 200
3 null


Instead of "inserting" new rows, why do you not rather UPDATE the initial rows with corresponding values of B column? Or, if you may "insert" row without initial value, MERGE them? You would not need to DELETE anything then.
Exact SQL command for this UPDATE/MERGE depends on the way "inserted" data are obtained, which you did not post.
Re: sql problem [message #436907 is a reply to message #436879] Tue, 29 December 2009 07:57 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Tue, 29 December 2009 12:45
select * from test110 a where  a.b is not null
        and  exists ( select 1 from test110 b where a.a = b.a and b.b is not null)

...

A slighty change in your query gives the result:
select * from test110 a 
where a.b is not null
   or ( a.b is null
      and not exists ( select null from test110 b where a.a = b.a and b.b is not null))

But follow flyboy's advice and change your application.

Regards
Michel


Previous Topic: Check Constraints with Date Ranges !!! URGENT
Next Topic: How '>' behaves while doing string comparison?
Goto Forum:
  


Current Time: Sun Dec 04 12:38:42 CST 2016

Total time taken to generate the page: 0.19035 seconds