help modifying key field

  • 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!!

  • update TableA set MONBR = LEFT(B.SAP_VALUE, 7)

       from TableA A, TableB B

     where A.MONBR = B.MONBR

        and B.SAP_NAME = 'CFG_PRODORDERNBR'

  • Greatly appreciated! Thanks Allen!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply