UPDATE only on last inserted rows

Question

UPDATE only on last inserted rows

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I'm importing some Excel spreadsheets via ETL into a relational database. The problem is that for me to be able to perform the relationship of the tables, I am using queries for the insertion of the foreign keys. But as the bank grows, these queries are taking an uncanny time. Is there any way to perform an UPDATE only on the last records that were included? For example:

UPDATE estrutura q
SET q.id_horizonte = (SELECT h.id_horizonte FROM horizonte h 
WHERE q.observacao = h.observacao AND q.nome_horizonte = h.nome_horizonte AND q.amostra_codigo = h.amostra_codigo)
LIMIT 50

It is not working that way, but my question is just this: is there any way for this UPDATE not to be applied to all records in the table and only in the last 50 included? This would greatly help with query performance.

mysql sql sql-update

asked by anonymous 11.11.2018 / 05:10

2 answers

Back to previous menu Foreign SQL key

score 2 · Answer 1

Try

UPDATE estrutura q
SET q.id_horizonte = 
(SELECT h.id_horizonte 
FROM horizonte h
WHERE q.observacao = h.observacao 
AND q.nome_horizonte = h.nome_horizonte 
AND q.amostra_codigo = h.amostra_codigo) 
WHERE q.id_horizonte  
IN 
(SELECT id_horizonte 
FROM estrutura 
ORDER BY id_horizonte DESC Limit 50)

score 0 · Answer 2

I was able to solve by putting a simple condition in my UPDATE. In my case it worked because the fields I want to update are always null.

UPDATE estrutura q
SET q.id_horizonte = (SELECT h.id_horizonte FROM horizonte h 
WHERE q.observacao = h.observacao AND q.nome_horizonte = h.nome_horizonte AND q.amostra_codigo = h.amostra_codigo)
WHERE id_horizonte IS NULL