SQLite3 UPDATE error sub-select returns 2 columns - expected 1

Question

I'm trying to update a flag in a phone number table

isbest : must be mobile and priority shoud be the closest to 0

cpf is FOREIGN KEY, so one id can have multiple phones, but only one will receive 'isbest' flag

UPDATE telefone SET isbest = 1 WHERE fullnumber in  (SELECT fullnumber,min(priority)
                    FROM telefone
                    WHERE phonetype = "MOBILE"
                    GROUP BY telefone.cpf
                    )

But I get this error:

sub-select returns 2 columns - expected 1

i've changet my code to

 UPDATE telefone SET isbest = 1 WHERE fullnumber in (SELECT fullnumber
                    FROM telefone
                    WHERE phonetype = "MOBILE"
                    GROUP BY telefone.cpf
                    ORDER BY priority asc
                    )

But then it update more numbers than expected (but the Subquery when run alone shows the right rows)

On the first code, I've tried to add another Subquery (select fullnumber from (...) ) but it also didn't help (Again, the subquery alone looks right)

i'm using SQLITE3 with SQLiteStudio

TL;DR when the code runs, at affects more rows than expected, but the subquery alone looks right

Your subquery does not make sense. Please add sample data to your question. — Tim Biegeleisen, Jul 15 '20 at 03:48
@TimBiegeleisen Subquery was inspired by the answer found in this question [link](https://stackoverflow.com/questions/4510185/select-max-value-of-each-group) instead of highest number, I need the lowest (+ a filter to remove landline number) — Anderson Palma, Jul 15 '20 at 04:55

score 0 · Accepted Answer · answered Jul 15 '20 at 07:24

Use = instead of IN and make sure that the correlated subquery will return only 1 row:

UPDATE telefone 
SET isbest = 1 
WHERE phonetype = 'MOBILE'
AND fullnumber = (
  SELECT t.fullnumber
  FROM telefone AS t
  WHERE t.cpf = telefone.cpf AND t.phonetype = telefone.phonetype
  ORDER BY t.priority LIMIT 1
)

SQLite3 UPDATE error sub-select returns 2 columns - expected 1

1 Answers1