RE: SQL Update query

From: Tom (Tom_at_discussions.microsoft.com)
Date: 08/19/04

  • Next message: Vishal Parkar: "Re: SQL Update query" 
    Date: Thu, 19 Aug 2004 08:41:04 -0700

    Hi Vishal,
    I need one more favor from your end. Based on the sql that you provided
    y'day, can we place 2 tables on the update clause. Requirement is to update
    both the tables based on the below criteria.

    begin transaction
    UPDATE S_ORG_EXT, EIM_ACCOUNT
    SET FUL_CENTER_FLG = 'Y'
    FROM S_ORG_EXT A, EIM_ACCOUNT B
    WHERE B.LOC = A.LOC
    and not exists
    (select * from S_EVT_ACT C where C.TARGET_OU_ID = A.ROW_ID )

    "Tom" wrote:

    > Hi,
    >
    > We are having problems while updating one table based on certain criteria.
    > In our scenario, there are 3 tables i.e EIM_ACCOUNT, S_ORG_EXT, S_EVT_ACT.
    >
    > Column definition of above table
    > EIM_ACCOUNT
    > ***********
    > ROW_ID
    > Name,
    > LOC
    >
    > S_ORG_EXT
    > ***********
    > ROW_ID,
    > NAME,
    > LOC
    > PR_FUL_REQ_FLG
    >
    > S_EVT_ACT
    > ************
    > ROW_ID
    > TARGET_OU_ID
    > TYPE
    >
    > The business requirement is to update PR_FUL_REQ_FLG with 'Y' of S_ORG_EXT
    > TABLE only if LOC of EIM_ACCOUNT = LOC of S_ORG_EXT and ROW_ID OF S_ORG_EXT
    > TABLE is not equal to TARGET_OU_ID OF S_EVT_ACT Table. The query that was
    > created for updating field is mentioned below
    >
    > UPDATE S_ORG_EXT
    > SET FUL_CENTER_FLG = 'Y'
    > FROM S_ORG_EXT A, EIM_ACCOUNT B, S_EVT_ACT C
    > WHERE B.LOC = A.LOC
    > AND A.ROW_ID NOT IN
    > (SELECT C.TARGET_OU_ID FROM S_EVT_ACT C)
    >
    > Can you please help identifying the problem !!!
    >
    > Thanks
    > Thom

  • Next message: Vishal Parkar: "Re: SQL Update query"

    Relevant Pages

    • Re: How do I modify database criteria at formula level?
      ... cell. ... a third column calculating the min Loc for that PN ... a fourth column calculating the max Loc for that PN ... But I need to vary the criteria at the function level... ...
      (microsoft.public.excel.worksheet.functions)
    • How do I modify database criteria at formula level?
      ... I would like to modify the criteria in the database functions DMIN and DMAX ... at the formula level, instead of by entering new value in criteria range cell. ... a third column calculating the min Loc for that PN ...
      (microsoft.public.excel.worksheet.functions)
    • Re: How do I modify database criteria at formula level?
      ... it never dawned on me that making "loc" as values solved part of my problem... ... cell. ... a fourth column calculating the max Loc for that PN ... But I need to vary the criteria at the function level... ...
      (microsoft.public.excel.worksheet.functions)
    • Problem with "Top N Value" Query
      ... I followed/modified the "Criteria" in Microsoft KB Q153747 but do not ... I only get the Top-10 across all groups ... ... FROM [tbl_Short Lines by LOC] ...
      (microsoft.public.access.queries)