| Update Issue.. [message #216100] |
Thu, 25 January 2007 00:27  |
senthildurga
Messages: 9
Registered: December 2006
|
Junior Member |
|
|
The file has only 16596 rows but it updated 17360 rows in database. Could you please analyze and find the reason why more rows are updated by End of date.Thanks in Advance...
|
|
|
|
|
|
|
|
| Re: Update Issue.. [message #216144 is a reply to message #216125] |
Thu, 25 January 2007 05:01  |
senthildurga
Messages: 9
Registered: December 2006
|
Junior Member |
|
|
Table Structure:
================
Name Null? Type
------------------------------------- -------- ------------
EMPLID(PRIMARY KEY) NOT NULL VARCHAR2(11)
COMPANY(Foreign KEY1) NOT NULL VARCHAR2(3)
EFFDT (Foreign KEY2) NOT NULL DATE
SPECIAL_FWT_STATUS NOT NULL VARCHAR2(1)
FWT_MAR_STATUS NOT NULL VARCHAR2(1)
FWT_ALLOWANCES NOT NULL NUMBER(38)
FWT_ADDL_AMT NOT NULL NUMBER(7,2)
FWT_ADDL_PCT NOT NULL NUMBER(5,3)
FUT_EXEMPT NOT NULL VARCHAR2(1)
EIC_STATUS NOT NULL VARCHAR2(1)
STATUTORY_EE NOT NULL VARCHAR2(1)
DECEASED NOT NULL VARCHAR2(1)
PENSION_PLAN NOT NULL VARCHAR2(1)
LEGAL_REP NOT NULL VARCHAR2(1)
DEFERRED_COMP NOT NULL VARCHAR2(1)
USE_TOTAL_WAGE NOT NULL VARCHAR2(1)
LAST_ACTION NOT NULL VARCHAR2(1)
W4_PRCSS NOT NULL VARCHAR2(1)
W5_PRCSS NOT NULL VARCHAR2(1)
LOCK_IN_RECVD NOT NULL VARCHAR2(1)
LOCK_IN_LIMIT NOT NULL NUMBER(38)
LAST_UPDATE_DATE DATE
DATE_OF_ENTRY DATE
COUNTRY NOT NULL VARCHAR2(3)
FORM_1001_RECD NOT NULL VARCHAR2(1)
FORM_1001_SUBMT_DT DATE
FORM_1001_EXP_DT DATE
FORM_8233_RECD NOT NULL VARCHAR2(1)
FORM_8233_SUBMT_DT DATE
FORM_8233_EXP_DT DATE
TAXPAYER_ID_NO NOT NULL VARCHAR2(9)
TREATY_EXP_DT DATE
TREATY_ID NOT NULL VARCHAR2(10)
FORM_1078_RECD NOT NULL VARCHAR2(1)
FORM_1078_SUBMT_DT DATE
THIRDPARTY_SICKPAY NOT NULL VARCHAR2(1)
W4_NAME_CHECK NOT NULL VARCHAR2(1)
PY_FLAG_FOR_IRS NOT NULL VARCHAR2(1)
PY_FLAG_FOR_IRS_DT DATE
CREATION_DT DATE
MARRIED_FILE_SINGL NOT NULL VARCHAR2(1)
NRA_EXEMPT_WHRULE NOT NULL VARCHAR2(1)
===============================================================
sql used...
UPDATE PS_FED_TAX_DATA F
SET F.PENSION_PLAN = 'N'
WHERE F.EFFDT = (SELECT MAX(EFFDT) FROM PS_FED_TAX_DATA
WHERE F.EMPLID = EMPLID
AND F.COMPANY = COMPANY
AND F.EFFDT <= SYSDATE)
After deploy the above update sql statement,it would fetch me 17360 rows instead of only 16596 rows.Moreove it has updated extra 764 rowsin database.I need the query that will update only 16596 rows in database.Could you please analyze and find the reason why more rows are updated in the table..
[EDIT - added CODE tags; next time, please, add them by yourself to improve readability]
[Updated on: Thu, 25 January 2007 05:43] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Update Issue.. [message #216158 is a reply to message #216100] |
Thu, 25 January 2007 05:38 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
I don't believe EMPLID on its own is the primary key. The structure of the update statement you've written implies that the primary key consists of at least:
EMPLID
COMPANY_ID
EFF_DATE
|
|
|
|
| Re: Update Issue.. [message #216165 is a reply to message #216144] |
Thu, 25 January 2007 07:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Run this piece of SQL:SELECT count(*)
FROM PS_FED_TAX_DATA F
WHERE F.EFFDT = (SELECT MAX(EFFDT) FROM PS_FED_TAX_DATA
WHERE F.EMPLID = EMPLID
AND F.COMPANY = COMPANY
AND F.EFFDT <= SYSDATE)
As @Littlefoot points out - if this includes some rows that you don't want to be processed, then you need to include additional clauses in the WHERE part of the query, or change the ones that are there.
How exactly do you know how many rows the query is meant to update?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
