Need to Merge two identical tables 1

[SenTnel]

I need to merge to identical tables, but one column (areas_sub_local_obj) has a reference to the Id column (Primary Key). I need a script to insert the data from table 1 into table two and to change the value in the column areas_sub_local_obj to the new Id that corresponds to the original number. Another words: When data is inserted and the original Id was 80 and the new Id is 890 then the rows that contain data in column areas_sub_local_obj that reference to the 80 Id needs to be change to 890 (the new Id) so the parent node relation does not break.

How can I do that?

 

[r937]

could you do a SHOW CREATE TABLE for both tables please

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SenTnel]

Thanks!

This is the table info.

CREATE TABLE `areas_sub_local_obj` (
`Id` INT(11) NOT NULL AUTO_INCREMENT,
`Code` VARCHAR(40) DEFAULT NULL,
`Nombre` VARCHAR(255) DEFAULT NULL,
`Fecha_Inscripcion` DATE DEFAULT NULL,
`Hospital` INT(11) DEFAULT NULL,
`areas_sub_local_obj` INT(11) DEFAULT NULL,
`type_category` ENUM('Area','Sub Area','Locales','Objeto') DEFAULT NULL,
`Client_data` INT(11) DEFAULT NULL,
`image` VARCHAR(255) DEFAULT NULL,
`Descripcion` VARCHAR(255) DEFAULT NULL,
`estado_obj` ENUM('Disponible','Ocupado') DEFAULT 'Disponible',
`lastdate_manteni` DATE DEFAULT NULL,
`condicion` ENUM('Nuevo','Usado','Adecuado','No Adecuado') DEFAULT NULL,
`gpieza` INT(11) DEFAULT NULL,
`gservicio` INT(11) DEFAULT NULL,
`gsuplidor` INT(11) DEFAULT NULL,
`f_ini_garantia` DATE DEFAULT NULL,
`localclass` VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=MYISAM AUTO_INCREMENT=377 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

 

[r937]

i see only one table

what's getting merged into what?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SenTnel]

The two tables are identical, one contains info from hospital 1, the other from hospital 2, I need to combine all the data in just one of the table, but, since both hospitals were running the application as "localhost" both tables started theirs Id's in no. 1, now I have both hospitals with different information using the same Id's (PK).

If I just insert the data from one into the other table then the Id's will change, but not the value in the "areas_sub_local_obj" column, wich leads back to the Id (PK) column, so it will break the parent node relation.

 

[r937]

update one of your tables, and change the PK by adding a large number to it

e.g. UPDATE table2 SET id = id + 4000000

do the same for any other columns which reference the id column

then you can simply INSERT/SELECT to add the contents of one table to the other

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SenTnel]

Nice! Superb! worked great! Thanks a lot!