1

I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below:

CREATE TABLE IF NOT EXISTS `zoom_city` (
  `id_ciudad` int(11) NOT NULL AUTO_INCREMENT,
  `id_estado` int(11) NOT NULL,
  `ciudad` varchar(200) NOT NULL,
  `capital` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_ciudad`),
  KEY `id_estado` (`id_estado`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=644;

CREATE TABLE IF NOT EXISTS `courrier_city` (
  `country` varchar(4) NOT NULL,
  `state` varchar(10) NOT NULL,
  `id` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `capital` varchar(255) NOT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedAt` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`country`,`state`,`id`),
  KEY `fk_courrier_city_state1_idx` (`country`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So I need to dump values from first table zoom_city and insert them in courrier_city but taking this in account:

  • country always be VE string
  • id_estado are stored in zoom_city as for example 1, 2, 3, 10 and right values for courrier_city and taking same example would be VE.01, VE.02, VE.03 and VE.10 so this value needs a transform before insertion.

Is that possible in raw SQL or I need to do this in a programming language like PHP for example?

1

I wasn't clear where you want the transformed id_estado inserted, so I chose "state" as a destionation. You can modify accordingly:

insert into courrier_city (country, state, created, modified) select "VE", if(is_estado<10, concat("VE0.",is_estado),concat("VE.",is_estado)), now(), now() from zoom_city

You will probably want to modify the example further to account for specifying all the destination columns with either a literal value such we did for country, a transform like we did for is_estado, or just the basic column name in the select portion to copy values.

You should specify some value for every destination column without a schema default defined.

  • I modified a bit the query and now looks like INSERT INTO courrier_city (country, state, created, modified) SELECT "VE", IF(id_estado<10, CONCAT("VE.0", id_estado),CONCAT("VE.", id_estado)), NOW(), NOW() FROM zoom_city but I get this error any time I try to run it [Err] 1062 - Duplicate entry 'VE-VE.01-' for key 'PRIMARY' why is that? – ReynierPM Mar 19 '14 at 14:11
  • Because you ran it twice and already had a copy of your primary key tuple, which enforces uniqueness I'm guessing. If you're doing a fresh run you can either truncate your destination table (I'll leave it to you if wiping everything out for a clean slate is appropriate), or look at the insert on duplicate key update syntax – atxdba Mar 19 '14 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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