CODEWITHSUNDEEP
sqlsybase
DSD
DSD

Reputation: 601

Update with self join in sybase

I have one table as below

COAR_ID COCE_ID COCI_TYPE BRKR_NM  AGNCY_NM
123      123      P        abc
123      231      G                 def
123      331      G                 ghi

I want to update AGNCY_NM for COCI_TYPE=P where max(COCE_ID) and COCI_TYPE='G'

so the output should be

COAR_ID COCE_ID COCI_TYPE BRKR_NM  AGNCY_NM
123      123      P        abc      ghi
123      231      G                 def
123      331      G                 ghi

i m using following update query

UPDATE Table
   SET
       AGNCY_NM = BB.AGNCY_NM
      ,UPDT_NBR = 3
    FROM
       Table AA
      ,Table BB
   WHERE
      AA.COAR_ID=BB.COAR_ID
      AND AA.COCI_TYPE='P'
      AND BB.AGNCY_NM <> ''
      AND AA.UPDT_NBR <> 3
      AND BB.COCE_ID=(SELECT MAX(CC.COCE_ID) FROM Table CC WHERE CC.COAR_ID=BB.COAR_ID )

it is working as expecting but i dont want to use same table again and agian as it may cause performance issue.

any better solution to update the column??

Thanks, Dhiraj

Upvotes: 1

Views: 2456

Answers (1)

Robert
Robert

Reputation: 25753

Try this way:

UPDATE TAB
   SET
       AGNCY_NM = BB.AGNCY_NM
      ,UPDT_NBR = 3
    FROM
       TAB BB
   WHERE
      TAB.COAR_ID=BB.COAR_ID
      AND TAB.COCI_TYPE='P'
      AND BB.AGNCY_NM <> ''
      AND TAB.UPDT_NBR <> 3
      AND BB.COCE_ID=(SELECT MAX(CC.COCE_ID) FROM TAB CC WHERE CC.COAR_ID=BB.COAR_ID )

Upvotes: 1

Related Questions