OraFAQ Forum: Forms » Updating all records with a single query

Home » Developer & Programmer » Forms » Updating all records with a single query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Updating all records with a single query [message #380498]

Mon, 12 January 2009 01:39

rakhatmis
Messages: 25
Registered: March 2008

Junior Member

Hi all
I am using forms 6i. One of my forms is calculating experience of employees by subtracting (sysdate-dateofjoining)/365 in post-text trigger which is working fine.

The database is having approx. 1000 records. Now what I want is on a single click of a button all the records will be updated i.e. on a single click of a button all the employee records will be having the latest (calculated from the current system date) experience of all the employees.

Kindly suggest me how this is going to be possible without going to each and every record.

Waiting for some early replies.

[MERGED by LF]

[Updated on: Tue, 27 January 2009 01:10] by Moderator

Report message to a moderator

Re: Updating all records with a single query [message #380511 is a reply to message #380498]

Mon, 12 January 2009 02:56

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Basically, this is an ordinary UPDATE statement which will run through all records stored in a table:

UPDATE this_table t SET
  t.experience = (sysdate - t.date_of_joining) / 365;

It has nothing to do with Forms - you can run it via SQL*Plus or any other client capable of running SQL statements. Of course, you may create a form with a push button which will contain such an UPDATE statement in the WHEN-BUTTON-PRESSED trigger if you wish.

Report message to a moderator

problem in update record [message #382988 is a reply to message #380498]

Mon, 26 January 2009 22:56

rakhatmis
Messages: 25
Registered: March 2008

Junior Member

Hello all
I am using forms 6i. I am calculating employee’s experience. I want to update the experience at a single click of a button according to sysdate. I am using the following code for this

DECLARE
	PCSIR_EXP NUMBER(3);
	RECORDCOUNT NUMBER(4);
	

 BEGIN
 	RECORDCOUNT := 0;
	FIRST_RECORD;
 	LOOP
 		
	PCSIR_EXP := (SYSDATE - (:PERSONALINFORMATION.DATEOFJOINING))/365;
	
	MESSAGE ('PCSIR EXPERIENCE = ' || TO_CHAR(PCSIR_EXP));
		UPDATE PERSONALINFORMATION
		SET PERSONALINFORMATION.PCSIRSERVICE = PCSIR_EXP
		WHERE PERSONALINFORMATION.CENTRE = 'HL';
		--AND PERSONALINFORMATION.EMPLOYEEID = '740016';
--		COMMIT;
		RECORDCOUNT := RECORDCOUNT+1;
	
		IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
			MESSAGE('AT LAST RECORD');
			EXIT;
		END IF;
		
		NEXT_RECORD;

 	END LOOP;
	MESSAGE('RECORDS UPDATED =  '||TO_CHAR(RECORDCOUNT));

	COMMIT;
 END;

My problem is that the experience is being calculated correctly but the last value calculated is inserted into all the records. I know there should be a silly mistake but I am not able to figure it out. Kindly help me

Waiting for some early replies.

[EDITED by LF: applied [code] tags]

[Updated on: Tue, 27 January 2009 04:05] by Moderator

Report message to a moderator

Re: problem in update record [message #383004 is a reply to message #382988]

Mon, 26 January 2009 23:43

azamkhan
Messages: 557
Registered: August 2005

Senior Member

Dear Mr. rakhatmis

What is happening is correct.

Actually what you are trying to do is, you are issuing an update command in a loop which is updating all records becuase you have'nt mention any specific employee.

UPDATE PERSONALINFORMATION
SET PERSONALINFORMATION.PCSIRSERVICE = PCSIR_EXP
WHERE PERSONALINFORMATION.CENTRE = 'HL';
[AND employee_id = your_form_item_employee_id]

I think you should do this...!

DECLARE
PCSIR_EXP NUMBER(3);
RECORDCOUNT NUMBER(4);

BEGIN

RECORDCOUNT := 0;
FIRST_RECORD;

LOOP

PCSIR_EXP := (SYSDATE - :PERSONALINFORMATION.DATEOFJOINING))/365;

MESSAGE ('PCSIR EXPERIENCE = ' || TO_CHAR(PCSIR_EXP));

:PERSONALINFORMATION.PCSIRSERVICE = PCSIR_EXP

RECORDCOUNT := RECORDCOUNT+1;

IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
MESSAGE('AT LAST RECORD');
EXIT;
END IF;

NEXT_RECORD;

END LOOP;

COMMIT;

MESSAGE('RECORDS UPDATED = '||TO_CHAR(RECORDCOUNT));

END;

Report message to a moderator

Re: problem in update record [message #383029 is a reply to message #383004]

Tue, 27 January 2009 00:30

rakhatmis
Messages: 25
Registered: March 2008

Junior Member

Thankyou very much Mr. Azam

it worked. but i was wondering why it was not working in forms
although it worked in Oracle SQL*Plus

anyhow thanks for timely help

miss rakhatmis

Report message to a moderator

Re: problem in update record [message #383044 is a reply to message #383029]

Tue, 27 January 2009 01:09

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

rakhatmis

i was wondering why it was not working in forms although it worked in Oracle SQL*Plus

I wouldn't say so; your UPDATE statement modified all records in the table where PERSONALINFORMATION.CENTRE = 'HL'.

Can you prove the statement I have quoted? Please, post a small test case and copy-paste your SQL*Plus session. Also, enclose your code into the [code] tags in order to preserve formatting.

Report message to a moderator

Re: problem in update record [message #383053 is a reply to message #383044]

Tue, 27 January 2009 02:17

rakhatmis
Messages: 25
Registered: March 2008

Junior Member

Respected Littlefoot
the output which i got when i run the code, i have posted, is

EMPLOYEEID PCSIRSERVICE
750576 23
620575 23
590577 23
590578 23
730579 23
590580 23
630581 23
590582 23
610583 23
590584 23
760585 23
610586 23
590587 23
590618 23
520619 23
620621 23

16 rows selected.

its because the value calculated was 23.

what can be the reason?

Report message to a moderator

Re: problem in update record [message #383056 is a reply to message #383053]

Tue, 27 January 2009 02:27

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm afraid you didn't understand what I asked.

You have said that the same code (you have used in a form) works properly in SQL*Plus. Therefore, I'd like you to post SQL*Plus session which proves this statement. It should look like my example posted in this topic and contain CREATE TABLE statement, INSERT INTO sample data, PL/SQL block which contains your UPDATE statement and, finally, SELECT FROM a table which would illustrate your claim.

Report message to a moderator

Re: problem in update record [message #383081 is a reply to message #383056]

Tue, 27 January 2009 04:02

rakhatmis
Messages: 25
Registered: March 2008

Junior Member

The update query works fine in oracle sql*plus because the point was on the update query. If I understand it right that here is the test case.

SQL> create table test_table (employeeid number(20), dateofjoining date,
   pcsirservice number(2), centre varchar(5));

Table created.

SQL> insert into test_table(employeeid, dateofjoining, centre)
  2  values (550005, '25-JUL-1981', 'HL');

1 row created.
…..

SQL> SELECT * FROM TEST_TABLE;

EMPLOYEEID DATEOFJOI PCSIRSERVICE CENTR
---------- --------- ------------ -----
    550005 25-JUL-81              HL
    580009 15-OCT-90              HL
    560010 21-OCT-87              HQ
    600011 07-SEP-89              HL
    480012 05-FEB-79              HQ

SQL> UPDATE TEST_TABLE
  2  SET PCSIRSERVICE = (SYSDATE-DATEOFJOINING)/365
  3  WHERE CENTRE = 'HL';

3 rows updated.

SQL> SELECT * FROM TEST_TABLE;

EMPLOYEEID DATEOFJOI PCSIRSERVICE CENTR
---------- --------- ------------ -----
    550005 25-JUL-81           28 HL
    580009 15-OCT-90           18 HL
    560010 21-OCT-87              HQ
    600011 07-SEP-89           19 HL
    480012 05-FEB-79              HQ

That was my claim that the update query is working fine in Oracle SQL*Plus but not in forms it just updates all the records with the last calculated value

[EDITED by LF: applied [code] tags]

[EDITED by DJM: split long line]

[Updated on: Wed, 28 January 2009 23:34] by Moderator

Report message to a moderator

Re: problem in update record [message #383088 is a reply to message #383081]

Tue, 27 January 2009 04:17

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yes, this is what I was talking about! Thank you! Just - please, next time enclose code into the [code] tags; it makes the code much more readable.

UPDATE statement you have posted:

SQL> UPDATE TEST_TABLE
  2  SET PCSIRSERVICE = (SYSDATE-DATEOFJOINING)/365
  3  WHERE CENTRE = 'HL';

updates all records where CENTRE = 'HL'. SELECT statement proves it.

Code you have posted in message #382988, which you have used in a form:

UPDATE PERSONALINFORMATION
SET PERSONALINFORMATION.PCSIRSERVICE = PCSIR_EXP
WHERE PERSONALINFORMATION.CENTRE = 'HL';

does exactly same: updates all records where CENTRE = 'HL'. Not absolutely all records in the table, only those where CENTRE = 'HL'. You have looped through all records displayed in a form, but for every form record you have updated all records in the table.

A form UPDATE statement would update a single record if you have specified that, such as

UPDATE PERSONALINFORMATION
SET PERSONALINFORMATION.PCSIRSERVICE = PCSIR_EXP
WHERE PERSONALINFORMATION.CENTRE = 'HL'
  AND personalinformation.employeeid = :form_data_block.employeeid   --> this line!

The output:

EMPLOYEEID PCSIRSERVICE
750576     23
620575     23 
590577     23
...

isn't descriptive as we don't see the CENTRE column. How did you get it? Which (UPDATE) statement did produce such a result?

Report message to a moderator

Previous Topic:	Regarding 6i to 10g Conversion
Next Topic:	username access

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:51:24 CST 2025