1

I am having trouble getting things moved in a database. I have two tables with different column names the one that is the same name is "zip" but they have the same data. I want to move from table A to table b if the zip code doesn't already exist in table b. I am using mysql command line.

Raidri
  • 17,258
  • 9
  • 62
  • 65
user1375893
  • 13
  • 3

1 Answers1

1

USe INSERT INTO....SELECT

INSERT INTO TableB(colName)
SELECT colName 
FROM    TableB b
        LEFT JOIN TableA a
            ON a.colName = b.colName
WHERE   a.colName IS NULL

TableA.colName will be NULL if it does not exists on TableB. just change colName to the columns names in your tables.

UPDATE 1

INSERT INTO skadate_location_city
    (
        Feature_int_id,
        Admin1_str_code,    
        Country_str_code,
        Feature_dec_lat,    
        Feature_dec_lon,
        zip
    )
SELECT  
        a.city_id     AS Feature_int_id,
        a.state_id    AS Admin1_str_code, 
        a.country_id  AS Country_str_code,
        a.latitude    AS Feature_dec_lat,
        a.longitude   AS Feature_dec_lon,
        a.zip
FROM    skadate_location_zip a
        LEFT JOIN skadate_location_city b
            ON a.zip = b.zip
WHERE   b.zip IS NULL
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Hi, I see you're new to SO. If you feel an answer solved the problem, please mark it as 'accepted' by clicking the green check mark. This helps keep the focus on older SO which still don't have answers. – John Woo Oct 07 '12 at 03:52
  • I think that may only work if the column names are the same. I cant get it to work. – user1375893 Oct 07 '12 at 04:14
  • can you post the query you have created? – John Woo Oct 07 '12 at 04:16
  • [code]insert into skadate_location_city(Feature_int_id, Admin1_str_code, Country_str_code, Feature_str_name, Feature_dec_lat, Feature_dec_lon, zip) select Feature_int_id, Admin1_str_code, Country_str_code, Feature_str_name, Feature_dec_lat, Feature_dec_lon, zip from skadate_location_city b left join skadate_location_zip a on a.zip = b.zip where a.zip is null; [/code] – user1375893 Oct 07 '12 at 04:28
  • what errors do you get? are columns `Feature_int_id, Admin1_str_code, Country_str_code, Feature_str_name, Feature_dec_lat, Feature_dec_lon, zip` from `skadate_location_city` AND `Feature_int_id, Admin1_str_code, Country_str_code, Feature_str_name, Feature_dec_lat, Feature_dec_lon, zip` from `skadate_location_city` ? you said that the have different column names right? could you include the schema of your tables please? – John Woo Oct 07 '12 at 04:32
  • ERROR 1046 (3D000): No database selected – user1375893 Oct 07 '12 at 04:36
  • choose the database first, eg `USE database;` – John Woo Oct 07 '12 at 04:38
  • yeah sorry the error is ERROR 1054 (42S22): Unknown column 'Feature_int_id' in 'field list' – user1375893 Oct 07 '12 at 04:40
  • we can't move to chat since you haven't enought reputation yet. to answer your question quickly, please provide the schema of your tables :D – John Woo Oct 07 '12 at 04:42
  • skadate_location_zip moving from to skadate_location_city – user1375893 Oct 07 '12 at 04:45
  • no, that's not what i want, I want the structures of your table. What are the columns in table `skadate_location_zip` as well as `skadate_location_city`. you need to put their correct column names. – John Woo Oct 07 '12 at 04:46
  • skadate_location_zip zip country_id state_id(index) city_id (index unique) latitude longitude // skadate_location_city Feature_int_id(city_id) Admin1_str_code(state_id) Country_str_code Feature_str_name (blank) Feature_dec_lat(latitude) Feature_dec_lon longitude zip – user1375893 Oct 07 '12 at 04:51
  • ok i tried it runs for a while and then ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY' i deleted the entry with 0 and ran again with the same result. – user1375893 Oct 07 '12 at 06:01
  • what is your primary key on table skadate_location_city? – John Woo Oct 07 '12 at 06:03
  • ok I got there was a few with 0 that worked great I want to thanks for your help. You saved me alot of time for moving them 1 at a time – user1375893 Oct 07 '12 at 06:04
  • Yo're welcome! If you feel an answer solved the problem, please mark it as 'accepted' by clicking the green check mark. This helps keep the focus on older SO which still don't have answers. – John Woo Oct 07 '12 at 06:06