CODEWITHSUNDEEP
mysqlsqlsql-updatesubquery
esseara
esseara

Reputation: 880

MySql - Update with Select

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"

Upvotes: 0

Views: 73

Answers (4)

esseara
esseara

Reputation: 880

This was the right syntax:

UPDATE t_stato_casa
SET
UTENTE = 'Admin'
FROM
    t_stato_casa AS sc
INNER JOIN T_CASA AS c
ON c.ID = sc.ID_CASA
WHERE
    c.ID_RICHIESTA in (437869, 437233, 437235, 437876)

Upvotes: 0

Tulip
Tulip

Reputation: 21

using alias, (MySQL)

update T_STATO_CASA as x
set x.UTENTE='Admin'
where x.ID_CASA in (
select ID from T_CASA
where ID_RICHIESTA in (437869, 437233, 437235, 437876)
);

Referencing of a table is needed to be done correctly.

Upvotes: 0

flyingfox
flyingfox

Reputation: 13506

The reason is that your subquery will return more than one row of record

The more suitable way is to use JOIN instead

  UPDATE T_STATO_CASA
    JOIN T_CASA t 
      ON t.id = ID_CASA 
     AND t.ID_RICHIESTA 
      IN (437869, 437233, 437235, 437876)
     SET UTENTE = 'Admin

If you still want to use subquery,you need to use group_concat to make the result into one record

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

UPDATE t_stato_casa x
  JOIN t_casa y
    ON y.id = x.id_casa
   SET x.utente = 'admin'  
 WHERE y.id_richiesta IN(437869, 437233, 437235, 437876)

Upvotes: 2

Related Questions