Update value with nested subqueries

oraclesubqueryupdate

I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C)

I have tried something like this which does work if I have only the first select statement but I need to exclude some records by using the second select statement.

update tableA set value1 = 'Y'
where tableB_ID in 
(
    select ID from tableB 
    where class in ('1','2')
) 
and tableC_ID in 
(
  select ID from tableC where name not like 'MPG%'
)

Best Answer

I find UPDATE statements, with complex WHERE clauses, are easier to understand, and maintain, when written as a MERGE statement.

Examples

MERGE INTO TableA a1
USING (
 SELECT TableA_ID, 'Y' value1
 FROM TableA a
 JOIN TableB b ON a.TableB_ID=b.TableB_ID
  JOIN TableC c ON a.TableC_ID=TableC_ID
WHERE
    b.class in ( '1', '2' )
  AND c.name NOT LIKE 'MPG%'
) b1
ON (a1.TableA_ID=b1.TableA_ID)
WHEN MATCHED THEN UPDATE
SET a1.value=b1.value
;

Related Solutions

Update with nested join problem

You can use NVL to return a default string when a null value is encountered:

SELECT NVL(fieldname,'Show this text instead of null')
FROM myTable;

In your case, I would try something like this:

SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",
   INITCAP(NVL(RE.LASTNAME,'No Referral')) AS "REFERRED LAST NAME", 
   INITCAP(NVL(RE.FIRSTNAME,'No Referral')) AS "REFERRED FIRST NAME" 
FROM CUSTOMERS CU
LEFT OUTER JOIN CUSTOMERS RE ON CU.REFERRED = RE.CUSTOMER#;

Updating Multiple Rows with Data from Column A into Column B

If you just want the discharge_dt to be overwritten with the admit_dt from the same row, it sounds like you just need

UPDATE patient
   SET discharge_dt = admit_dt
 WHERE facility_id = 'X'
   AND pat_seq IN (<<query that returns the 60,000 keys>>)

Best Answer

Related Solutions

Update with nested join problem

Updating Multiple Rows with Data from Column A into Column B

Related Question