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.

user2073606
  • 327
  • 1
  • 3
  • 9
  • 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.. – user2073606 Feb 17 '13 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. – ESG Feb 17 '13 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 – user2073606 Feb 17 '13 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. – Martijn Pieters Feb 17 '13 at 21:04

1 Answers1

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.

user2079098
  • 132
  • 8