SQL - UPDATE Commands fails when using LIKE

Question

I'm performing a bulk update on a column within a table. I need to change the current date from NULL to a past date.. Which I know works fine when performning against a single account. But when using a WILDCARD, this seems to fail.

Any ideas what my issue is, can I not use LIKE in subquery..

SET message.archived_at = (SELECT TO_CHAR(systimestamp-31, 'DD-MON-YY HH.MI.SS')
FROM dual)
WHERE EXISTS = (SELECT entity_id FROM user_info
WHERE UPPER(user_info.directory_auth_id) like 'USER%')

I have 10,000 records that I need to update..

I've changed to

UPDATE message SET message.archived_at = (SELECT TO_CHAR(systimestamp-31, 'DD-MON-YY HH.MI.SS') FROM dual) WHERE EXISTS (SELECT entity_id FROM user_info WHERE UPPER(directory_auth_id) like 'JLOADUSER1001%')

the SELECT query in the WHERE EXISTS section, when run by itself returns 10 users ID.. But when the whole query is run, this updates 1.8 million rows.. expected result is ~1500 rows..

is the user_info.directory_auth_id beginning with USER? Also, please add the error msg.. — VF_
– VF_, Commented Apr 29, 2014 at 9:35
SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" *Cause: *Action: — Boardman411
– Boardman411, Commented Apr 29, 2014 at 13:12

Olesya Razuvayevskaya · Accepted Answer · 2014-04-29 09:35:36Z

2

LIKE clauses are allowed in Oracle subqueries and UPDATE statements. The line that seems erroneous is:

 WHERE EXISTS = (SELECT entity_id FROM user_info

Use:

 WHERE EXISTS  (SELECT entity_id FROM user_info

instead

answered Apr 29, 2014 at 9:35

Olesya Razuvayevskaya

1,1681 gold badge8 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Boardman411 Over a year ago

Looks like my SQL query is ignoring the SELECT statement when updating - all rows are updated in the 'message' table.. Once I remove the = after WHERE EXISTS.

Olesya Razuvayevskaya Over a year ago

You get all the rows updated because you have not specified any relationship between the first table and user_info. Could you specify the logical relationship between these two tables?

Collectives™ on Stack Overflow

SQL - UPDATE Commands fails when using LIKE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related