change distinct rows if exists or insert or remove

Question

I have table:

CREATE TABLE tbl_teams (            
    id integer  primary key, 
    team_id varchar(80), 
    team_title varchar(80), 
    team_city varchar(80)
)

from server comes some list of active teams, for example in JSON-format:

{
    "teams":[
    {
            "team_id":678,
            "title":"Red Heads",
            "town":"Moscow"
    },
    {
            "team_id":90,
            "title":"Blue Hands",
            "town":"Baghdad"
    },
    {
            "team_id":128,
            "title":"White Tails",
            "town":"Paris"
    }
    ]}

How set table tbl_teamsto actual state? I mean, title and town can be changed, constant only team_id. Teams can be changed, or inserted if team_id not exists in table, or removed if not exists in server response.

Hope for help.

Check this question: http://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update — CFreitas, Oct 09 '15 at 12:07
No, i don't wont replace all rows. In this case more convinient will be erase all table and fill it again) I want replace only different, somethig as `IF ((team_id_table == team_id_new) AND (title_table != title_new OR town_table != town_new)) THEN REPLACE ELSE (IF (team_id_table != team_id_new) THEN INSERT)` — user3444737, Oct 09 '15 at 13:56

score 0 · Answer 1 · answered Oct 09 '15 at 21:47

Answer from another forum:

UPDATE tbl_teams
    SET team_title=(SELECT title FROM NewTable WHERE 
             NewTable.team_id=tbl_teams.team_id), 
        team_city=(SELECT town FROM NewTable WHERE
             NewTable.team_id=tbl_teams.team_id)
    WHERE EXISTS(SELECT *
        FROM NewTable
             WHERE NewTable.team_id=tbl_teams.team_id and
                   (NewTable.title<>tbl_teams.team_title or
                    NewTable.town<>tbl_teams.team_city))

INSERT INTO tbl_teams (team_id,team_title,team_city)
    SELECT team_id,title,town
        FROM NewTable
            WHERE NOT EXISTS(SELECT * FROM tbl_teams WHERE
                 NewTable.team_id=tbl_teams.team_id)

change distinct rows if exists or insert or remove

1 Answers1