Logical JDBC problem
All of my tables are now linked correctly and i am able to send all of my data from java to all of my tables. This problem i am having now is not really to do with sql, unless there is somthing in sql that can solve this problem. I have now set my Meet table up in my database rather than doing it through java. Now my problem lies with my Events table. This is how i am inserting into it (as i say, it will be changed to PreparedStatement)
String sql = "INSERT INTO tblEvent (Event_Name, Meet_ID) " +
"SELECT '"+ eveType2 + "', ID FROM tblMeet " +
And heres how i insert into the rounds table
So say i insert the first load of data, say 100M Run and Round 1, it all goes through ok. Now i do my second load of data, say 100M Run and Round 2, this is where i have a problem. Now first time round, both INSERTS created a row. Second time round, INSERT INTO tblEvent creates another row as it still see's Meet_Name = "Wembley" from my tblMeet.
String sql = "INSERT INTO tblRound (Round_Number, Event_ID) " +
"SELECT '"+ roundType2 + "', ID FROM tblEvent " +
"WHERE Event_Name='" + eveType2 + "'";
And then the second statement runs, and it has the line "WHERE Event_Name='" + eveType2 + "'";. Now this statement will see two rows where the Event_Name = 100M Run, and will create two new rows in its table for Round_2. So basically extra rows are being created. This is where i am having trouble. Whats the usual way to overcome this? Is there somthing i can do to tell it to only create one row each time ro should i instead do the data for these two tables in my database?
It seems like your data structure is a little screwy. Without seeing more of it, I would say change the first statement to only insert if the row doesn't already exist.
I think my formalisation of my tables is ok, but because whenever i update my tables, everything gets updated, thats whats causing me the problem. But being able to tell it to only insert if row does not exsist would solve this problem. But i was also thinking about hardcoding it into my db. Will see how it goes.
I'm definitely not a DBA but I know you can prevent duplicate rows in your DB. When you try to add a duplicate, it'll throw an error back at you so you'll have to be sure to handle that in your code.