Copy row to identical table - mysql

Asked

Viewed 60 times

1

Well, I have 2 identical tables in the mysql database, where I registered photos in one, and forgot the other. How do I copy the data from the field foto of TABLE 1 for TABLE 2. But only in that field nome are equal in both.

TABLE 1 = patients TABLE 2 = backup patients.

I tried that code, but you’re not recognizing it from WHERE.

 INSERT INTO pacientes_backup (foto) SELECT foto from pacientes WHERE pacientes_backup.nome = pacientes.nome;
mysql

  • Assuming that are all photos and that are in the same order. Can be so: INSERT INTO pacientes_backup (foto) SELECT foto from pacientes

  • @Reginaldorigo, all right? So man, it’s because TABLE 2 has more records than TABLE 1...

3 answers

3

You need to be very attentive to the field you will use as a key, as you may end up updating all rows of the backup table and apparently it is not what you want. I used temporary tables to illustrate the solution:

DECLARE @pacientes TABLE (Nome VARCHAR(20), Foto VARCHAR(10))
DECLARE @pacientes_backup TABLE (Nome VARCHAR(20), Foto VARCHAR(10))
INSERT INTO @pacientes VALUES ('Ronaldo', 'exame.jpg')
INSERT INTO @pacientes_backup VALUES ('Ronaldo',null)


SELECT * 
  FROM @pacientes

SELECT * 
  FROM @pacientes_backup -- SEM FOTO

UPDATE pb SET pb.Foto = p.Foto --ATUALIZANDO A LINHA DA TABELA BACKUP COM A FOTO LOCALIZADA NA TABELA PRINCIPAL
  FROM @pacientes p
 INNER JOIN @pacientes_backup pb ON pb.nome = p.nome


SELECT * 
  FROM @pacientes

SELECT * 
  FROM @pacientes_backup -- COM FOTO

You can see that I didn’t use INSERT but yes UPDATE by the fact of the line already exist and do not have the value in the PHOTO field.

I hope I helped. Good job.

  • Thank you for the reply, however, the first declare is in error...

  • Cool! As I did in SQL Server for having some difference, but runs smooth in SQL Server. Hugs.

1

Working here, simple and practical.

UPDATE pacientes_backup
SET pacientes_backup.foto =(SELECT pacientes.foto FROM pacientes WHERE pacientes.nome = pacientes_backup.nome)

0

Try it this way, and see if it worked:

INSERT INTO pacientes_backup_novo(foto)
SELECT p.foto FROM pacientes p, 
      pacientes_backup pk 
WHERE p.nome = pk.nome;

But before you do, make sure that the bank is empty, as it is an insert and not an update.

And check that the other fields accept null, otherwise you will not be able to execute this query.

Important: You are trying to make an insertion comparing a name that is in the two tables, IE, you will never get, because if both tables have data, you should not do Insert, but update the data.

If you only want to import photos from one table to another, you can do so:

update pacientes_backup bk set bk.foto = (
select foto from pacientes p inner join pacientes_backup in_bk  on in_bk.nome = p.nome) 
where bk.name = (
select nome from pacientes p inner join pacientes_backup in_bk  on in_bk.nome = p.nome)

or:

UPDATE pacientes_backup pk
SET pk.foto =(SELECT p.foto FROM pacientes p WHERE p.nome = pk.nome)

Browser other questions tagged mysql

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