0

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
5
  • Please show us the output of SHOW CREATE TABLE insc_mwc\G Aug 5, 2014 at 14:56
  • Umm add your value after 0?
    – Mihai
    Aug 5, 2014 at 14:59
  • I posted an answer, but I can make me answer if you post the output of SHOW CREATE TABLE insc_mwc\G Aug 5, 2014 at 15:11
  • Is nome a UNIQUE value ? Aug 5, 2014 at 15:26
  • Please run SELECT COUNT(1) FROM insc_mwc; and post the output Aug 5, 2014 at 15:27

1 Answer 1

0

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) > 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;
2
  • You're adding an integer to a VARCHAR?
    – Vérace
    Aug 5, 2014 at 15:33
  • my ideia its add a value, generated by me, and that value is the same for each time this query is executed, i give you an exemple, now I have 10 itens in temp_mwc, i want copy this 10 itens to insc_mwc and add the same value of n_registo, and if I add more n_registo have a different value, I know im a complicated person Aug 5, 2014 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.