Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Unanswered: how to update 2 tables in one update statement

    i m trying to update 2 tables in single update statement. but it's not working
    ...

    Create procedure abc()
    BEGIN


    UPDATE lrp_data, fam_class_mdata SET s_jan = gro_sales / 12,
    s_feb = gro_sales / 12,
    s_mar = gro_sales / 12,
    s_apr = gro_sales / 12,
    s_may = gro_sales / 12,
    s_jun = gro_sales / 12,
    s_jul = gro_sales / 12,
    s_aug = gro_sales / 12,
    s_sep = gro_sales / 12,
    s_oct = gro_sales / 12,
    s_nov = gro_sales / 12,
    s_dec = gro_sales / 12,
    userid = 'evergreen',
    dbtimestamp = CURRENT_TIMESTAMP
    WHERE fam_class_mdata.prd_family_type = 'A' AND fam_class_mdata.prd_family = lrp_data.gl_prd_family and lrp_data.year = 2009;

    END


    it shows n error

    sqlcode:-104

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I don't think this is allowed ...

    Why do you want to do in one stmt ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Update works on a single table only.

    In DB2 LUW, you can apply an update to a SELECT statement - but only if there is a unique mapping from a single column/value to the underlying base tables.

    In your case, this seems to be unnecessary. You should just update LRP_DATA and use a subselect to filter the rows based on some content in FAM_CLASS_MDATA.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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