Error while using update with inner join and select and where condition

Question

I have some error when i tried to update on row in my database

 update tbl_status 
    set document_status=11 
    from tbl_status
    inner join tbl_log on tbl_status.id=tbl_log.id 
    where tbl_status.document_status=7 and tbl_log.IDS=662;

error :

SQL command not properly ended

any solution

score 0 · Answer 1 · answered Oct 12 '21 at 19:22

0

In Oracle, that would be

update tbl_status a set
  a.document_status = 11
  where a.id = (select b.id       --> possibly where a.id IN (select ...
                from tbl_log b
                where b.ids = 662
               )
    and a.document_status = 7;

Or, with merge:

merge into tbl_status a
  using (select b.id
         from tbl_log b
         where b.ids = 662
        ) x
  on (a.id = x.id)
  when matched then update set
    a.document_status = 11
    where a.document_status = 7;

answered Oct 12 '21 at 19:22

Littlefoot

131,892
15
35
57

"single-row subquery returns more than one row" i have this error – drali Oct 12 '21 at 19:45
Didn't you see the comment I posted along with the 3rd line in UPDATE code? Use IN instead of = – Littlefoot Oct 12 '21 at 19:49

Error while using update with inner join and select and where condition

1 Answers1