update data from one table from another in SQL

Question

update data from one table from another in SQL

Navigation

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

0

I would like to know if it is possible, and how it is done, to update data from one table by others from another.

For example:

Table 1:

id_tabla1 | Nombre | Descripcion

Table 2:

id_tabla2 | Nombre | Descripcion

The difference between table 1 and 2, is that the 1 has the first 2 records that can not be touched, that is, they should always be fixed, yes or yes.

Table 2 is updated every day (Data is added and modified) What I need is to insert all the data from table 2 in the 1. Either by inserting the new ones and updating the old ones, or by deleting all the data from table 1 minus the first 2 records and then inserting those from table 2.

Can it be done?

Table 1 is in MYSQL and 2 in SQL Server. I have them related, that is, from the sql server I do the queries of the mysql tables. An example would be:

id_tabla1 | Nombre | Descripcion  
1                  a               a1  
2                  b               b1  
3                  c               c1

Table 2:

id_tabla2 | Nombre | Descripcion
1                  a               a1
2                  b               b1
3                  c               c2
4                  d               d1

Then what I need is to insert in table 1 the line of id 4 of table 2 and update the line of id 3.

mysql sql sql-server

asked by Pepemujica 03.11.2016 в 02:56

source

3 answers

Django deployed in Heroku. Administrative interface does not look good Using ListBox to view directory files - Visual C ++

score 1 · Answer 1

This solution is for two MySql tables. To begin, I will give you tables with test data, so that it is understood.

As you mentioned, the first two records can not be deleted. Starting from this, I do not complicate my life and I delete all the records of table 1 minus those two, with this query.

DELETE FROM Tabla_1 WHERE IdT1 <> 0 OR IdT1 <> 1;

Now, you have your table 1 clean, you just have to copy all the records in Table 2 in Table 1, imagine Table 2, so that:

To copy all the records, you use this query:

INSERT INTO Tabla_1 SELECT * FROM Tabla_2;

Control errors, that's how your application works.

Better control to avoid PK error in table 1 when inserting data of Table 2. You use this query.

INSERT INTO Tabla_1 SELECT * FROM Tabla_2 WHERE IdT2 <> 0 OR IdT2 <> 1;

You make sure that if there is any record with Id with a value of 0 or 1 in table 2, do not enter it, since you do not need it, since you said that the first two records in Table 1 do not touch each other , and thus you avoid the PK error with Table 1.

score 0 · Answer 2

It occurs to me that you could add an attribute to those records that is mandatory and that would serve you for queries as boolean , so if that value is 0 the record can not be deleted and if it is 1 yes .

In this way you would make a delete of all records where campo = 1 , and then insert the values you want and add the value 0/1 depending on whether in the future you will want to delete it.

score -1 · Answer 3

I would solve it with two questions. First an update updating all those that cross and then another query with an insert of the ones you do not have in the first one. Trying to solve it in a single consultation will complicate your life in excess. I have built some sql with the structure that you show (on the fly). I hope you serve as a reference:

update tabla1 
set nombre = tabla2.nombre, 
descripcion = tabla2.descripcion 
from tabla1 
inner join tabla2 on tabla1.id = tabla2.id; 

insert into tabla1(id,nombre,descripcion) 
select tabla2.id,tabla2.nombre,tabla2.descripcion 
from tabla2 
left join tabla1 on tabla1.id=tabla2.id 
where tabla1.id is null;