CODEWITHSUNDEEP
mysqlsqlsql-updatesubquery
Sabrina T.
Sabrina T.

Reputation: 23

Update field in a table with values from another field from another table

I need to update the last 110 values ​​of the descricao_geral table with the last 110 values ​​of the relevo table

I'm doing this: 

UPDATE descricao_geral
SET id_relevo_fk = (SELECT id_relevo FROM relevo ORDER BY id_relevo DESC LIMIT 110) 
ORDER BY id_descricao DESC
LIMIT 110

The error I've received:

Subquery returns more than 1 row

Upvotes: 1

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is really tricky. You need to join the tables together, but you don't have an appropriate key.

You can use variables to assign a sequential value and then use this for the join:

update descricao_geral g join
       (select (@rng := @rng + 1) as rn, g2.id_descricao
        from (select g2.* from descricao_geral g2 order by g2.id_descricao desc) g2 cross join
             (select @rng := 0) params
        limit 110
       ) g2
       on g.id_descricao = g2.id_descricao join
       (select (@rnr := @rnr + 1) as rn, r.id_relevo
        from (select r.* from relevo r order by r.id_relevo desc) r cross join
             (select @rnr := 0) params
       ) r
       on g2.rn = r.rn
   set g.id_relevo_fk = r.id_relevo;

Upvotes: 1

Related Questions