Home » SQL & PL/SQL » SQL & PL/SQL » Update Issue..
Update Issue.. [message #216100] Thu, 25 January 2007 00:27 Go to next message
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 #216125 is a reply to message #216100] Thu, 25 January 2007 02:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Show us a table description and the SQL you used.

Re: Update Issue.. [message #216127 is a reply to message #216125] Thu, 25 January 2007 02:48 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"File" is a file, right? The one stored in a directory on your disk? Just to make sure that you do not mean file = table (a table stored in an Oracle database).

So, if this file really is a file, perhaps there are 16596 "meaningful" rows in there, but the rest of 17360 - 16596 = 764 rows are empty (as if you pressed <Enter> 764 times after the last line).
Re: Update Issue.. [message #216144 is a reply to message #216125] Thu, 25 January 2007 05:01 Go to previous messageGo to next message
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 #216147 is a reply to message #216144] Thu, 25 January 2007 05:11 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that you need to include additional restriction into the WHERE clause.
Re: Update Issue.. [message #216156 is a reply to message #216100] Thu, 25 January 2007 05:34 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Does PS_FED_TAX_DATA have a PRIMARY/UNIQUE key ? If so, what is it ?
Re: Update Issue.. [message #216158 is a reply to message #216100] Thu, 25 January 2007 05:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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)
This is the number of rows that your UPDATE will process.

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?
Re: Update Issue.. [message #216174 is a reply to message #216165] Thu, 25 January 2007 07:40 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Could it be that there are 764 records with a sysdate of today?
If so, use trunc(sysdate) instead of sysdate
Previous Topic: convert date
Next Topic: query help / analytic functions / lead+lag
Goto Forum:
  


Current Time: Sat Dec 03 10:13:25 CST 2016

Total time taken to generate the page: 0.12603 seconds