Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Am trying to update single column with multiple values (Y/N) by correlated update and getting error could use:

ORA-01427: single-row sub-query returns more than one row.

I have two tables.

REQUESTS_TABLE

+-------------+------+
| CUSTOMER_ID | FLAG |
+-------------+------+
|         200 |      |
|         900 |      |
+-------------+------+
CREDITS_TABLE
+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
|         100 | John          |
|         200 | Smith         |
|         300 | Mary          |
|         400 | David         |
|         500 | Jake          |
+-------------+---------------+
If customer_id from requests table exists in credits table then I want to update "FLAG" column in requests table as 'Y'. If not found then 'N'. Below is the output am trying to get:
OUTPUT :
REQUESTS_TABLE
+-------------+------------+
| CUSTOMER_ID | FOUND_FLAG |
+-------------+------------+
|         200 | Y          |
|         900 | N          |
+-------------+------------+
Below is the query that I tried which is resulting in error:
UPDATE requests_table r 
SET    ( flag ) = (SELECT CASE 
                            WHEN c.customer_id IS NOT NULL THEN 'Y' 
                            ELSE 'N' 
                   FROM   credits_table c 
                   WHERE  c.customer_id = r.customer_id) 
WHERE  EXISTS (SELECT * 
               FROM   credits_table c 
               WHERE  c.customer_id = r.customer_id) 
When I searched online for the help I found queries that updates multiple columns but not multiple values in single column. Hence seeking help here.
Appreciate any help. 
Thanks,

Richa
UPDATE requests_table
SET found_flag = CASE WHEN EXISTS(SELECT c.customer_id FROM credits_table c 
                                 WHERE c.customer_id = requests_table.customer_id)
                      THEN 'Y'
                      ELSE 'N'
                Hi @Lukasz Szozda, thanks for quick response. Query worked like a charm. I appreciate your help. I would like to inform that it took almost 10 minutes to update the rows. Table had nearly 85k rows. Is there a way to improve the performance? Am thinking to replace * with customer_id, will it increase the performance?
– Richa
                Oct 10, 2018 at 18:50
                @Richa Adding index could helpCREATE INDEX r_idx ON requests_table(customer_id) and CREATE INDEX c_idx ON credits_table(customer_id).
– Lukasz Szozda
                Oct 10, 2018 at 18:51
You might be better off doing this as two statements (ideally in a single transaction):
UPDATE requests_table
SET    found_flag = 'N';
UPDATE requests_table
SET    found_flag = 'Y'
WHERE  EXISTS
           (SELECT *
            FROM   credits_table c
            WHERE  c.customer_id = requests_table.customer_id);
This is likely to perform better than @LukaszSzozda's answer, as it avoids the nested sub-query which would need to be run for every row in the requests_table.
Another option for doing this in a single query, which should be of a comparable speed is to use merge (I tend to use merge over update for all but the simplest updates).
MERGE INTO requests_table
USING      (SELECT rt.customer_id,
                   CASE WHEN c.customer_id IS NULL THEN 'N' ELSE 'Y' END
                       AS found_flag
            FROM   requests_table rt
                   LEFT JOIN credits_table c
                       ON c.customer_id = rt.customer_id) src
ON         (requests_table.customer_id = src.customer_id)
WHEN MATCHED THEN
    UPDATE SET found_flag = src.found_flag
                Hi @Allan, I tried query with merge statement but am getting following error: ORA-00904: "SRC"."FOUND_FLAG": invalid identifier
– Richa
                Oct 10, 2018 at 20:08
                wow awesome. Thank you so much @Allan. I highly appreciate your help and superfast response.
– Richa
                Oct 10, 2018 at 20:10
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.