0 
cur.execute("""INSERT INTO football.match_events(type, player, time, game_id) VALUES (%s, %s, %s, %s)""", (etype, player_id, time, game_id))

I am completing a project where I scrape and process soccer text commentaries. This INSERT statement is part of a section of code that processes yellow cards. Unfortunately as these commentaries are updated live there are sometimes corrections (although they don't say correction so I can't filter for them). This has led me to have two rows for one booking as it appeared twice in the commentary.

I am looking for an easy way for the SQL statement to check whether all the fields in the query already exist in the table together in a single row. I hope that makes sense.

Improve this question
4
  • Hi, I must have not explained it well enough. I am starting to think it isn't something that can be done in SQL. There is a primary key, auto incr. But none of these items are meant to be unique. Ie there can be lots of match_events of the same type, there can be lots of match events performed by the same player and there can even be multiple match events performed at the same time, its the combination of these 4 variables that I want to check for. Maybe it is something I will need to do in python.. Commented Feb 17, 2013 at 18:27
  • You can create a unique key containing those 4 columns. Replace/on duplicate key update will work on any unique key, not only the primary key. Commented Feb 17, 2013 at 18:40
  • hi, thanks. How do I do this? Do you mean construct it in python before i INSERT or is there a way to set the table up to do this in phpmyadmin Commented Feb 17, 2013 at 18:44
  • @user2073606: You create a UNIQUE constraint on those four columns (UNIQUE KEY (col1, col2, col3, col4)), then use the INSERT ... ON DUPLICATE KEY UPDATE syntax when inserting, and MySQL will automatically update the existing row instead if it matches those 4 columns. That's why I linked you to the duplicate. Commented Feb 17, 2013 at 21:04

1 Answer 1

Reset to default
0

You could use a REPLACE INTO (assuming MySQL) for this to work for you. It will insert the value if it doesn't exist, or replace the existing entry if it does.

You should also create an appropriate key, perhaps based off game_id in your example, so that the existing entry can be updated.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.