Results 1 to 3 of 3

Thread: update problem

  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Question Unanswered: update problem

    Hi,

    I need help with the following SQL. Here are my table structures.

    TableA
    -------------
    MONBR VARCHAR(12)

    TableB
    -------------
    MONBR VARCHAR(12)
    SAP_NAME VARCHAR(30)
    SAP_VALUE VARCHAR(20)

    1. Table A actually stores a 7 character MONBR.
    2. Table B actually stores a 7 character MONBR.
    3. However Table B also stores the true 12 character MONBR in the SAP_VALUE column, where the SAP_NAME='CFG_PRODORDERNBR'.
    4. I want to update TableA.MONBR with the value in TableB.SAP_VALUE where TableA.MONBR=TableB.MONBR and TableB.SAP_NAME='CFG_PRODORDERNBR'

    The following query will not suffice since the subquery returns multiple values:

    update TableA set MONBR=(
    select B.SAP_VALUE from TableB B, TableA C
    where B.SAP_NAME='CFG_PRODORDERNBR'
    and c.MONBR=B.MONBR)

    Can anyone help me with the SQL for this?

    Thanks!!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    update a set MONBR = b.SAP_VALUE
    from TableA a
    inner join TableB b
    on a.MONBR = b.MONBR
    where b.SAP_NAME='CFG_PRODORDERNBR'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    2

    thanks

    excellent. thanks!

Posting Permissions

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