| Update Query with Returning Clause [message #620283] |
Wed, 30 July 2014 23:48  |
 |
cklh2000
Messages: 8
Registered: January 2013
|
Junior Member |
|
|
I have a query written in PostgreSQL. This will pick up the records from job_information table with the status as supplied through application (ex : 'READY_TO_RUN') and with limit of records as supplied through application (ex : 100), and then updates the job_information with app given status (ex : 'ACQUIRED') and returns that set (means, returns total job_information table data for those got updated with the given status) of records for application usage.
I am trying to translate this query into Oracle SQL (please see the below for my Oracle SQL). I know the way I did it is incorrect can someone please give me some advice on how to do it correctly? Thank you!!
PostgreSQL Query:
UPDATE job_information AS J1
SET status=?
FROM
(SELECT job_name,
job_group,
created_date
FROM job_information
WHERE status =?
AND CURRENT_TIMESTAMP >= scheduled_execution_time
ORDER BY scheduled_execution_time limit ?
) AS J2
WHERE J1.job_name = J2.job_name
AND J1.job_group = J2.job_group
AND J1.created_date = J2.created_date RETURNING *;
Sample Case:
UPDATE job_information AS J1
SET status= 'ACQUIRED'
FROM
(SELECT job_name,
job_group,
created_date
FROM job_information
WHERE status = 'READY_TO_RUN'
AND CURRENT_TIMESTAMP >= scheduled_execution_time
ORDER BY scheduled_execution_time limit 100
) AS J2
WHERE J1.job_name = J2.job_name
AND J1.job_group = J2.job_group
AND J1.created_date = J2.created_date RETURNING *;
MY ORACLE CODE:
UPDATE JOB_INFORMATION SET STATUS=
(
WITH J2 as (
select job_name, job_group, created_date from (SELECT job_name, job_group, created_date FROM job_information WHERE status= :b and current_timestamp >= scheduled_execution_time order by scheduled_execution_time ) where rownum<= :c
)
SELECT :a FROM JOB_INFORMATION J1, J2 WHERE J1.job_name = J2.job_name AND J1.job_group = J2.job_group AND J1.created_date = J2.created_date
);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Update Query with Returning Clause [message #620357 is a reply to message #620356] |
Thu, 31 July 2014 12:20  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Error at command line, most likely you have executed the script in your client. Next is the error report, to which BS has already suggested. You have this piece of code in plsql and not sql.
Would you, please, post your entire session using copy paste. You must be using a tool other than SQL*Plus, else, where is the line number in the error stack?
[Updated on: Thu, 31 July 2014 12:22] Report message to a moderator |
|
|
|
| Re: Update Query with Returning Clause [message #620358 is a reply to message #620355] |
Thu, 31 July 2014 12:21 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It doesn't have to be in a procedure, but you do need a bind variable. Does this help:
orclz>
orclz> update emp set sal=1000 where ename='KING' returning deptno;
update emp set sal=1000 where ename='KING' returning deptno
*
ERROR at line 1:
ORA-00925: missing INTO keyword
orclz> var n number
orclz>
orclz> update emp set sal=1000 where ename='KING' returning deptno into :n;
1 row updated.
orclz>
orclz> print n
N
----------
10
orclz>
|
|
|
|
| Re: Update Query with Returning Clause [message #620359 is a reply to message #620358] |
Thu, 31 July 2014 12:24 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
upon further review it appears to me that every row in JOB_INFORMATION will have STATUS set to the same value.
Is the the desired results?
UPDATE job_information
SET status = (WITH j2
AS (SELECT job_name,
job_group,
created_date
FROM (SELECT job_name,
job_group,
created_date
FROM job_information
WHERE status = 'b'
AND current_timestamp >=
scheduled_execution_time
ORDER BY scheduled_execution_time)
WHERE ROWNUM <= 100)
SELECT 'a'
FROM job_information J1,
j2
WHERE J1.job_name = j2.job_name
AND J1.job_group = j2.job_group
AND J1.created_date = j2.created_date)
returning job_name, job_group bulk collect INTO l_job_info;
[Updated on: Thu, 31 July 2014 12:28] Report message to a moderator |
|
|
|
|
|
| Re: Update Query with Returning Clause [message #620373 is a reply to message #620359] |
Thu, 31 July 2014 14:49 |
|
cklh2000
Messages: 8
Registered: January 2013
|
Junior Member |
|
|
BlackSwan and all,
You are so correct, I just realized this will cause update all rows in the table. Which is NOT WHAT I wanted.
Let me give out a simpler sample below:
CREATE TABLE TEST
(
STATUS VARCHAR(50),
TS TIMESTAMP (9) DEFAULT SYSTIMESTAMP,
JOB_NAME VARCHAR(50)
);
INSERT INTO TEST VALUES ('ACTIVE',SYSDATE,'1');
INSERT INTO TEST VALUES ('ACTIVE',SYSDATE,'2');
INSERT INTO TEST VALUES ('INACTIVE',SYSDATE,'1');
INSERT INTO TEST VALUES ('INACTIVE',SYSDATE,'2');
INSERT INTO TEST VALUES ('INACTIVE',SYSDATE,'3');
INSERT INTO TEST VALUES ('ACTIVE',SYSDATE,'3');
INSERT INTO TEST VALUES ('ACTIVE',SYSDATE,'4');
INSERT INTO TEST VALUES ('INACTIVE',SYSDATE,'4');
INSERT INTO TEST VALUES ('INACTIVE',SYSDATE,'5');
INSERT INTO TEST VALUES ('INACTIVE',SYSDATE,'6');
COMMIT;
select * from test order by status, job_name;
STATUS TS JOB_NAME
-------------------------------------------------- ------------------------------- --------------------------------------------------
ACTIVE 31-JUL-14 12.40.12.000000000 PM 1
ACTIVE 31-JUL-14 12.40.12.000000000 PM 2
ACTIVE 31-JUL-14 12.40.12.000000000 PM 3
ACTIVE 31-JUL-14 12.40.12.000000000 PM 4
INACTIVE 31-JUL-14 12.40.12.000000000 PM 1
INACTIVE 31-JUL-14 12.40.12.000000000 PM 2
INACTIVE 31-JUL-14 12.40.12.000000000 PM 3
INACTIVE 31-JUL-14 12.40.12.000000000 PM 4
INACTIVE 31-JUL-14 12.40.12.000000000 PM 5
INACTIVE 31-JUL-14 12.40.12.000000000 PM 6
10 rows selected
What I want is to update the TEST table only the rows with the condition shown as below and return all columns in the query. Thank you!!
with T2 as (select status, job_name from TEST where status='ACTIVE')
select * from TEST c1, T2 where c1.job_name=T2.job_name;
STATUS TS JOB_NAME STATUS JOB_NAME
-------------------------------------------------- ------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
ACTIVE 31-JUL-14 12.40.12.000000000 PM 1 ACTIVE 1
ACTIVE 31-JUL-14 12.40.12.000000000 PM 2 ACTIVE 2
INACTIVE 31-JUL-14 12.40.12.000000000 PM 1 ACTIVE 1
INACTIVE 31-JUL-14 12.40.12.000000000 PM 2 ACTIVE 2
INACTIVE 31-JUL-14 12.40.12.000000000 PM 3 ACTIVE 3
ACTIVE 31-JUL-14 12.40.12.000000000 PM 3 ACTIVE 3
ACTIVE 31-JUL-14 12.40.12.000000000 PM 4 ACTIVE 4
INACTIVE 31-JUL-14 12.40.12.000000000 PM 4 ACTIVE 4
8 rows selected
|
|
|
|
|
|
| Re: Update Query with Returning Clause [message #620378 is a reply to message #620374] |
Thu, 31 July 2014 15:17 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
There is no way to display a collection but looping.
Display is the ONLY purpose of the loop in my example just to show the UPDATE fills the collections.
You can do what you want with the collection with or without looping depending on your needs.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
