So i have a JTable displaying my search results. It has three columns, Nationality, First_Name and Last_Name, and 9 rows of data. Now i have decided for the sake of simplicaty till i get used to this, when the user clicks the update button, the new table model will just replcae everything in the database, so no need for any model listeners or hash codes, lol, etc.
So this is how it should be:

JTable displaying
9 rows of data

User edits this JTable

New table model sent
to database and replaces
table with the old data

So i have started the statement
Code:

String sql = "UPDATE tblCompetitor " +"SET Nationality = ?, First_Name = ?, Last_Name = ? " +"WHERE roundType2 = ? " +"AND eveType2 = ?";

This is mainly related to java which is why the sql statement looks different. And i know i shouldnt be using parameters but java allows you to do this which is why i have the ? What i really need to know is whether i am doing the format of the update correctly. If you had a table in your database, with 3 columns and 9 rows, and you wanted to update it completely with a whole new set of 9 rows, how would you perform this using the update statement?
cheers

you will have to perform 9 UPDATE queries, one for each records, but yea the syntax is correct.

PHP Code:

UPDATE myTable SET field1='value1', field2='value2' WHERE field1='condition1' 


Does the WHERE clause have to be on field 1? My WHERE is done on a different condition. When i run this query in my database, it ask me to enter only 1 value for the SET, And 2 for event name and round type, but it doesnt append any rows.

Originally Posted by nick2price

If you had a table in your database, with 3 columns and 9 rows, and you wanted to update it completely with a whole new set of 9 rows, how would you perform this using the update statement?
cheers

All update statements internally delete the old row and put a new one in its place. You could explicitly model that as a delete and then an insert command within a single transaction, but why?

Stoot98 brings up a more relevant concern. Either you need to fire off an individual update command on a row per row bases, identifying the specific row you'd like to update (ideally by PK) with the specific new values, or find a way to make this work with set theory.

Really it should be two tables which are updated, but i am unsure if you can do this. My table are as follows

tblMeet
Id (PK)
Meet_Name
 
tblEvent
Id(PK)
Event_Name
Meet_ID(FK)
 
tblRound
ID(PK)
Round_Number
 
tblCompetitor
ID(PK)
Nationality
First_Name
Last_Name
 
tblResults
Competitor_ID(FK)
Event_ID(FK)
Round_ID(FK)
Result

So you can see tblResults, has control over most of the foriegn keys. So now i want to change all rows in my tblCompetitor where Round_ID = 1 and Event_Name = 100M Run. I can do this insert one by one because i will just do it through a loop in java. So what would this update statement look like? Would it be simular to how i have done it because at the moment the way i have it will not work.