Solved

Oracle SQL Update Statement Query Syntax

Posted on 2016-08-24
3
65 Views
Last Modified: 2016-08-25
Hello Experts,

I've created the following two temporary tables:

TABLE JS_ARRESTS (with the following fields)

CASE_ID
FILING_DATE
CASE_NUMBER
OFFENSE_DATE
ARREST_DATE
CHRG_NBR
DGOF_CD
CHARGE
CHRG_DESC
DFNDT_LAST_NAME
DFNDT_FIRST_NAME
DFNDT_MIDDLE_NAME
DFNDT_DOB
DFNDT_SSN
ARRAIGNMENT_DATE

TABLE JS_ARRAIGNMENT_EVENTS (with the following fields)

CASE_ID
CASE_NUMBER
COURT_DATE
EVENT_ID
COURTROOM
EVENT_CODE
EVENT_DESC
RESULT_DATE
RESULT_CODE
RESULT_DESC

I want to update the ARRAIGNMENT_DATE field in the JS_ARRESTS table
with the COURT_DATE values in the JS_ARRAIGNMENT_EVENTS table
whenever the JS_ARRESTS.CASE_ID = JS_ARRAIGNMENT_EVENTS.CASE_ID
and the JS_ARRAIGNMENT_EVENTS.COURT_DATE > JS_ARRESTS.ARREST_DATE.

I found the following update statement online, but when I use it in SQL Developer, I get the error message below.

update js_arrests
set arraignment_date = (select court_date
from js_arraignment_events
where js_arrests.case_id = js_arraignment_events.case_id
and js_arraignment_events.court_date > js_arrests.arrest_date)
where exists (select court_date
from js_arraignment_events
where js_arrests.case_id = js_arraignment_events.case_id
and js_arraignment_events.court_date > js_arrests.arrest_date);

Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:
0
Comment
Question by:jsmith08
3 Comments
 
LVL 4

Accepted Solution

by:
Abhimanyu Suri earned 250 total points
ID: 41769638
Because below mentioned SQL must be returning more than 1 record

select court_date
from js_arraignment_events
where js_arrests.case_id = js_arraignment_events.case_id
and js_arraignment_events.court_date > js_arrests.arrest_date

Assuming, you want the latest court_date as per table js_arraignment_events, please use something like

select court_date from (select court_date
from js_arraignment_events
where js_arrests.case_id = js_arraignment_events.case_id
and js_arraignment_events.court_date > js_arrests.arrest_date
order by court_date_desc) CRT_DT where rownum < 2
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 41769809
Please review this article I wrote to handle "updates with join":
https://www.experts-exchange.com/articles/1517/UPDATES-with-JOIN-for-everybody.html
0
 

Author Closing Comment

by:jsmith08
ID: 41770046
Thank you very much for your help! I've accomplished what I needed to do.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now