update data from one table from another in SQL

Question

update data from one table from another in SQL

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 Table 2 is that Table 1 has the first 2 records that cannot be touched, that is, they must 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 inserting the new ones and updating the old ones, or deleting all 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 Table 2 is in SQL Server. I have them related, that is, from sql server I do the queries of 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

So what I need is to insert in Table 1 the id line 4 of Table 2 and update ID Line 3.

0

Author: Pepemujica, 2016-11-03

Source

3 answers

score 2 · Answer 1

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

As you mentioned, the first two records cannot be deleted. Based on this, I do not complicate my life and 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 from Table 2 into Table 1, let's imagine the Table 2, such that:

To copy all records, you use this query:

INSERT INTO Tabla_1 SELECT * FROM Tabla_2;

Control errors, that already as your application works.

Better control to avoid PK error in Table 1 when inserting data from 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 value of 0 or 1 in Table 2, do not enter it, since you do not need it, since you have said that the first two records in Table 1 are not touched, 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 that if that value is 0 the record cannot be deleted and if it is 1 yes.

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

score -1 · Answer 3

I would solve it with two queries. First an update updating all those that cross and then another query with an insert of those that you do not have in the first. Trying to solve it in a single query will complicate your life excessively.I have built some sql with the structure you show (on the fly). I hope they serve you 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;