Update query with select for Procedure

Asked

Viewed 153 times

2

I’m trying to do a query that will enter a previous but I’m not succeeding, the query is like this:

UPDATE 
  sca_lotacao AS t0 
SET 
  t0.ativo = 0 
WHERE (
    SELECT * 
    FROM 
      sca_pessoa AS t1
      RIGHT JOIN sca_lotacao AS t2 ON t1.idLotacao = t2.idLotacao
    WHERE 
      t1.matricula IS NULL);

Error:

Operand should contain 1 column(s)

I know there really must be something wrong, but I’m trying to figure this out. In SELECT I do to make the selection of all fields of Capacity that does not have registration and assign to them the value of 0 (inactive), but I do not know if this logic is very well.

From what I can see I can even bring in all the seats that have no one.

mysql sql query

1 answer

1


If your intention is to update all fields where records exist that meet such condition, you should use the operator EXISTS

UPDATE 
  sca_lotacao AS t0 
SET 
  t0.ativo = 0 
WHERE 
  NOT EXISTS(
    SELECT * 
    FROM sca_pessoa AS t1 
    WHERE t1.idLotacao = t0.idLotacao);

  • That number 1 in the ... EXISTS (SELECT 1 FROM.. is that right? Anyway I have this error now: You can’t specify target table 't0' for update in FROM clause

  • @phpricardo the one is a constant, since the exists does not need to bring any data. I will revert to have the JOIN no from. It has been a long time since I used mysql to remember that it does not allow the use of the field in Update’s WHERE. Thanks for the feedback

  • Hehe I helped you remember? Cara pq no longer needs JOIN ?

  • 1

    @phpricardo I’ll have to work better on Join, but I have to run with something here. With JOIN tb it is not correct because it is not filtering for each record from the updated table to set. as soon as I have an answer put here and notify you.

  • Another detail is that now is no longer giving error, but the "no resulset"

  • 1

    You can fix this! UPDATE sca_manning AS t0 SET t0.active = 0 WHERE NOT EXISTS(SELECT * FROM sca_person AS t1 WHERE t1.idLotacao = t0.idLotacao);

  • 1

    @phpricardo blz, I will update with your query! vlw

Show 2 more comments

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.