Link to home
Create AccountLog in
Avatar of Janice Smith
Janice SmithFlag for United States of America

asked on

Oracle SQL Update Statement Query Syntax

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:
ASKER CERTIFIED SOLUTION
Avatar of Abhimanyu Suri
Abhimanyu Suri
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Janice Smith

ASKER

Thank you very much for your help! I've accomplished what I needed to do.