move content from one table to another table and add a new value I need to move content from one table to another table, this part is done:

INSERT INTO `insc_mwc`
(`nome`,`empresa_idempresa`,`cat_mwc_idcat`,`points_pontos`,`n_registo`)
SELECT nome,empresa_idempresa,cat_mwc_idcat,0
FROM temp_mwc WHERE temp_mwc.empresa_idempresa=$id;


How I can give value to n_registo, this value is the same for each time the query is executed.

output of  SHOW CREATE TABLE insc_mwc

CREATE TABLE `insc_mwc` (
 `idwater` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(45) DEFAULT NULL,
 `categoria` varchar(45) DEFAULT NULL,
 `empresa_idempresa` int(11) NOT NULL,
 `mwccar_idcat` int(11) NOT NULL,
 `points_pontos` int(11) NOT NULL,
 `n_registo` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idwater`),
 KEY `fk_water_empresa1_idx` (`empresa_idempresa`),
 KEY `fk_water_mwccar1_idx` (`mwccar_idcat`),
 KEY `fk_water_points1_idx` (`points_pontos`),
 CONSTRAINT `fk_water_empresa1` FOREIGN KEY (`empresa_idempresa`) REFERENCES `empresa` (`idempresa`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_mwccar1` FOREIGN KEY (`mwccar_idcat`) REFERENCES `cat_mwc` (`idcat`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_points1` FOREIGN KEY (`points_pontos`) REFERENCES `points` (`pontos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Your table needs to be like this

CREATE TABLE `insc_mwc` (
 `idwater` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(45) NOT NULL, -- Make nome NOT NULL
 `categoria` varchar(45) DEFAULT NULL,
 `empresa_idempresa` int(11) NOT NULL,
 `mwccar_idcat` int(11) NOT NULL,
 `points_pontos` int(11) NOT NULL,
 `n_registo` INT DEFAULT 0,
 PRIMARY KEY (`idwater`),
 UNIQUE KEY (`nome`), -- Add unique index on nome
 KEY `fk_water_empresa1_idx` (`empresa_idempresa`),
 KEY `fk_water_mwccar1_idx` (`mwccar_idcat`),
 KEY `fk_water_points1_idx` (`points_pontos`),
 CONSTRAINT `fk_water_empresa1` FOREIGN KEY (`empresa_idempresa`) REFERENCES `empresa` (`idempresa`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_mwccar1` FOREIGN KEY (`mwccar_idcat`) REFERENCES `cat_mwc` (`idcat`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_points1` FOREIGN KEY (`points_pontos`) REFERENCES `points` (`pontos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1


To get this, first make sure nome is unique by running this:

SELECT nome,COUNT(1) DupCount FROM insc_mwc GROUP BY nome HAVING COUNT(1) &gt; 1;


If you get nothing back (Empty Set) from this query, then you can safely run

ALTER TABLE insc_mwc MODIFY n_registo INT DEFAULT 0, ADD UNIQUE INDEX (`nome`);


With a unique nome and n_registo as an INT, you can increment n_registo for each time there is a DUPLICATE KEY on nome. The INSERT can now be done like this:

INSERT INTO `insc_mwc`
(`nome`,`empresa_idempresa`,`cat_mwc_idcat`,`points_pontos`)
SELECT nome,empresa_idempresa,cat_mwc_idcat
FROM temp_mwc WHERE temp_mwc.empresa_idempresa=$id
ON DUPLICATE KEY UPDATE n_registo = n_registo + 1;