May 15, 2009 at 9:40 am
I built testdata that consists of the records i want to udpate in CUST.TABLE2.
The select records is 10 rows that should be update.
You can see that having a inner join does not give me more records.
--18 records updated
UPDATE T2
SET UDEF_ID = 'FMP'
FROM TESTDATA T1
INNER JOIN CUST.TABLE2 T2 ON T1.GENL_ID = T2.GENL_ID
WHERE
T1.UDEF_ID IN ('F', 'FLS', 'FLP', 'FMLP', 'FMLS', 'FMPS') and
T1.GENL_ID = T2.GENL_ID
--10 records updated
UPDATE CUST.TABLE2
SET CUST.TABLE2.UDEF_ID = 'FMP' ,
MODIFIED_BY = 'SCRIPT'
FROM TESTDATA ---from the work table built which has the data
WHERE
TESTDATA.GENL_ID = CUST.TABLE2.GENL_ID AND
CUST.TABLE2.UDEF_ID IN ('F', 'FLS', 'FLP', 'FMLP', 'FMLS', 'FMPS')
Any ideas why inner join gives more records updated then not defining it.
Basically just want to update main table CUST.TABLE2 if the records are found in TESTDATA
Cheers
May 15, 2009 at 9:48 am
What does this query return?
SELECT T2.*
FROM TESTDATA T1
INNER JOIN CUST.TABLE2 T2 ON T1.GENL_ID = T2.GENL_ID
WHERE
T1.UDEF_ID IN ('F', 'FLS', 'FLP', 'FMLP', 'FMLS', 'FMPS') and
T1.GENL_ID = T2.GENL_ID
May 15, 2009 at 10:41 am
Gives 33 records so added another part of the key UDEF_LBL_KEY
I included another part of key
SELECT T2.*
FROM TESTDATA T1
INNER JOIN CUST.TABLE2 T2 ON T1.GENL_ID = T2.GENL_ID AND A.UDEF_LBL_KEY = B.UDEF_LBL_KEY
WHERE
T1.UDEF_ID IN ('F', 'FLS', 'FLP', 'FMLP', 'FMLS', 'FMPS') and
T1.GENL_ID = T2.GENL_ID and
AND A.UDEF_LBL_KEY = B.UDEF_LBL_KEY
Now this gives the 10 records to update.
So the update here didn't work so added the additional key
--18 records updated with additional key of LBL_KEY works and gives 10 records
UPDATE T2
SET UDEF_ID = 'FMP'
FROM TESTDATA T1
INNER JOIN CUST.TABLE2 T2 ON T1.GENL_ID = T2.GENL_ID AND
T1.UDEF_LBL_KEY = T2.UDEF_LBL_KEY
WHERE
T1.UDEF_ID IN ('F', 'FLS', 'FLP', 'FMLP', 'FMLS', 'FMPS') and
T1.UDEF_LBL_KEY = T2.UDEF_LBL_KEY and
T1.GENL_ID = T2.GENL_ID
This update worked regardless of the additional key LBL_KEY - This bit im confused on and why.
works without the key UDEF_LBL_KEY
--10 records updated
UPDATE CUST.TABLE2
SET CUST.TABLE2.UDEF_ID = 'FMP' ,
MODIFIED_BY = 'SCRIPT'
FROM TESTDATA ---from the work table built which has the data
WHERE
TESTDATA.GENL_ID = CUST.TABLE2.GENL_ID AND
CUST.TABLE2.UDEF_ID IN ('F', 'FLS', 'FLP', 'FMLP', 'FMLS', 'FMPS')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply