Home » SQL & PL/SQL » SQL & PL/SQL » different on upate in (oracle and sqlserver)
different on upate in (oracle and sqlserver) [message #428987] Sat, 31 October 2009 10:36 Go to next message
zxx2403
Messages: 11
Registered: November 2006
Location: china
Junior Member

table a
ship_date dely_day
20090101 2
20090102 2
20090103 2
20090104 2
20090105 2
table b
work_day
20081228
20081230
20081231
20090101
---------------------------------------------
update a
set ship_date = (select top 1 work_day from b where b.workday < a.ship_date order by workday)

in sqlserver
it work ok

but in oracle
it does not work ship_date is the seem value
why

thanks for you help

Re: different on upate in (oracle and sqlserver) [message #428991 is a reply to message #428987] Sat, 31 October 2009 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it does not work ship_date is the seem value

I don't know what does this mean but "top 1" is NOT part of Oracle SQL.

Regards
Michel
Re: different on upate in (oracle and sqlserver) [message #429048 is a reply to message #428987] Sun, 01 November 2009 14:23 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Michel Cadot wrote on Sat, 31 October 2009 11:53
Quote:
it does not work ship_date is the seem value

I don't know what does this mean but "top 1" is NOT part of Oracle SQL.

Regards
Michel


As Sir said, there is no 'top 1' in oracle.


You can do it by using ROWNUM. You can find some alternatives as well if you read ANALYTICAL FUNCTIONS.Do a search on Google or here in this forum itself by the keyword 'Nth maximum'.

Here is an example, hope it will help you!


create table t0101 select level col1 from dual connect by level <=10;

select * from t0101;
/

1
2
3
4
5
6
7
8
9
10


select col1 from ( select col1 from t0101 order by col1 desc) where rownum=1;
/
10

[Updated on: Sun, 01 November 2009 14:31]

Report message to a moderator

Previous Topic: SQL to compare current and previous record values and display them (merged)
Next Topic: Interesting MINUS problem (merged)
Goto Forum:
  


Current Time: Sat Oct 01 05:57:19 CDT 2016

Total time taken to generate the page: 0.15607 seconds