CODEWITHSUNDEEP
sqloracle-database
RemoveBeforeFlight
RemoveBeforeFlight

Reputation: 3

SQL command not ended properly error; trying to update information with a join

UPDATE A
SET A.email_from = B.new_email_from
FROM email_list A 
INNER JOIN update B 
    ON A.email_subject = B.email_subject 
WHERE A.process = B.process;

I am trying to update a table with updated information that another table contains. I'm not sure why it keeps giving me this error.

ORA-00933: SQL command not properly ended

Upvotes: 0

Views: 58

Answers (3)

Ankit Mongia
Ankit Mongia

Reputation: 210

Hi @RemoveBeforeFight,

Please try updating using correlated sub query.

update a set A.email_from = (
  select B.new_email_from 
    from update B 
    where a.email_subject = b.email_subject 
    and A.process = B.process
);

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

A simple MERGE will do

MERGE INTO a 
USING b 
ON ( a.email_subject = b.email_subject ) 
WHEN matched THEN 
  UPDATE SET a.email_from = b.new_email_from 
WHERE a.process = b.process;

Upvotes: 2

Shawn
Shawn

Reputation: 4786

You have invalid syntax for Oracle. It should be much simpler to do an UPDATE on a JOIN, but it is what it is.

Try something like:

UPDATE 
  ( 
    SELECT A.email_from, B.new_email_from
    FROM email_list A 
    INNER JOIN update B ON A.email_subject = B.email_subject 
        AND A.process = B.process
) s1
SET s1.email_from = s1.new_email_from

Upvotes: 0

Related Questions