CODEWITHSUNDEEP
selectdb2
beetri
beetri

Reputation: 1049

db2 query to select the first row fetched

I have a query like 

UPDATE PRD_PRODUCT_L10N ppl
 SET ( CATCHING_PHRASE
    , GENERIC_NAME
    , INGREDIENTS
    , QUANTITY
    , DOSE
    , NUTRITION_FACTS
    , PRODUCT_DESCRIPTION
    , PROMOTION_MESSAGE
    , MESSAGE
   ) = (
   SELECT distinct CATCHING_PHRASE
        , GENERIC_NAME
        , INGREDIENTS
        , QUANTITY
        , DOSE
        , NUTRITION_FACTS
        , PRODUCT_DESCRIPTION
        , PROMOTION_MESSAGE
        , MESSAGE
    FROM  TEMP_UPLOAD_PRODUCT_ATTRIBUTES tupa
    INNER JOIN
          PRD_PRODUCT                    pp
      ON  pp .EISIDENTIFIER = tupa.EISIDENTIFIER
    WHERE ppl.PRODUCTGUID   = pp.GUID
      AND ppl.LOCALEGUID     = tupa.LOCALEGUID
   )
 WHERE EXISTS (
   SELECT 0
    FROM  TEMP_UPLOAD_PRODUCT_ATTRIBUTES tupa
    INNER JOIN
          PRD_PRODUCT                    pp
      ON  pp .EISIDENTIFIER = tupa.EISIDENTIFIER
    WHERE ppl.PRODUCTGUID   = pp  .GUID
      AND ppl.LOCALEGUID     = tupa.LOCALEGUID
   )

the subquery returns more than 1 row and I would like to insert the first selected. How do I do that in DB2 database?

Please advice.

Thanks

Upvotes: 14

Views: 83866

Answers (2)

bhamby
bhamby

Reputation: 15450

Add FETCH FIRST ROW ONLY to your subquery. Search for fetch-first-clause on the page linked for more info. This is for DB2 on Linux/Unix/Windows.

If you're on the Mainframe (v9), then you want this page for more info (or version 10).

Upvotes: 5

Tadeu Maia
Tadeu Maia

Reputation: 1194

Depending on your DB2 version (i think 8 upwards) you can use fetch at your subquery

(select * from table fetch first 1 rows only)

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.admin%2Ffrstnrw.htm

Upvotes: 28

Related Questions