job.answiz.com
  • 1
Votes
name
name Punditsdkoslkdosdkoskdo

MySql - Update with Select in sql.

I need to update a table with a select inside. This is my query so far:

Update T_STATO_CASA
  Set  UTENTE = 'Admin'
Where ID_CASA in (
                  Select ID 
                    From T_CASA 
                   Where ID_RICHIESTA 
                      In (437869, 437233, 437235, 437876)
                  )

But it returns the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows

=, !=, <, <= , >, >=

or when the subquery is used as an expression." The subquery returns exactly 4 results if I run it separately. Is my syntax wrong? Thanks.

EDIT: all the suggested solutions that are using JOIN give me syntax error, as if MySql expects only the update-set-where command sequence. For instance I cannot write something like

update T_STATO_CASA as x
set [...]
where [...]

because it gives me syntax error: "Incorrect syntax near word AS. Expected SET"

name
  • 1
Teacher

In Mysql , there are two type of locking to avoid such situation in innodb engine

  • table level locking

  • row level locking

    You will need row level locking for this

  • 1
Reply Report