July 14, 2004 at 12:28 pm
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!!
July 14, 2004 at 1:25 pm
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'
July 14, 2004 at 1:32 pm
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