Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Unanswered: Convert "update from" in Sybase to Oracle.

    Hi, how would I convert the "update from" in the following Sybase SQL to a compatible version in Oracle without having to go via the nasty cursor route.

    update 	security
    set 	security.security_number =s.security_number,
            security.security_date = s.security_date,
    	security.qualifier = s.qualifier,
    	security.description = s.description,
    	security.currency_code = s.currency_code,
    	security.share_class = s.share_class,
    	security.asset_group = s.asset_group,
    	security.mod_dtm = getdate(),
    	security.status_date = s.status_date,
    	security.check_date = @date,
    	security.is_deleted = "N"
    from  	iosecurity s
    where 	s.security_number = security.security_number and
    	(s.security_date = security.security_date or (s.security_date is null and security.security_date is null)) and
    	s.qualifier = security.qualifier and
    	(security.status_date <= s.status_date or security.status_date is null or s.status_date is null)

  2. #2
    Join Date
    Mar 2007
    although you did not say what is that UPDATE supposed to do, it should conform to this type of syntax:
    update security
    set ( security_number, <other columns>, security.is_deleted ) =
        ( select s.security_number, <other expressions>, 'N'
          from iosecurity s
          where <your where conditions> )
    where exists ( select 1 from iosecurity s where <your where conditions> )
    If that UPDATEs all rows in SECURITY (those without corresponding entry in IOSECURITY with NULL), remove the last line (WHERE EXISTS ...).
    You will also have to replace GETDATE() and @date expressions with equivalent Oracle ones.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts