Paul Clapham wrote:So now you have several records with the same first and last name. And you only want to keep one of them. Does it matter which one? I ask because there might be three, for example, all having different data in them. How do you choose which of the three to keep, or do you care? Or would it be all right to end up with only one record which contains some data from each of the three?
Paul Clapham wrote:Okay, I understand now. Your database table has a problem: it doesn't have a primary key. And without a primary key you won't be able to write a query identifying a particular record and hence won't be able to delete a particular record.
The usual way to deal with that is to have an integer column named something like "UserID" and to declare it as auto-increment, so that the database has the responsibility to assign unique numbers and you don't.
Robert Ingmarsson wrote:How can i correctly update an userId column in my table model class so that it matches up with the
current row count in my JTable?
Paul Clapham wrote:
Robert Ingmarsson wrote:How can i correctly update an userId column in my table model class so that it matches up with the
current row count in my JTable?
I don't understand that. What you need is for each row to contain the actual userId which is in the database. And the normal way to do that is to get the userID from the database immediately after you write the record and the database assigns a value to that column.
I also notice that in your original post your query only reads the name fields. If you need the userId field (and you do, if you're going to delete records) then your query should read that as well.
Robert Ingmarsson wrote:Could you show some code on how to do this? Is the userID column you are talking about the same as the primary key column?
Paul Clapham wrote:
Robert Ingmarsson wrote:Could you show some code on how to do this? Is the userID column you are talking about the same as the primary key column?
Here's how to write a query which reads the ID column as well as the name columns:
But were you really asking that? I couldn't quite tell what "this" means in "how to do this".
Paul Clapham wrote:without a primary key you won't be able to write a query identifying a particular record and hence won't be able to delete a particular record.
Robert Ingmarsson wrote:the values in the id column does not match up with the actual amount of rows in the model.
Brian Tkatch wrote:
Paul Clapham wrote:without a primary key you won't be able to write a query identifying a particular record and hence won't be able to delete a particular record.
Both of those statements are incorrect. Some databases have a method to identify individual records. Oracle has ROWID, and others have similar setups. But that doesn't matter anyway, as the query itself can provide an id, via ROW_NUMBER(), or some other analytical function, or even Oracle's ROWNUM or equivalent in other databases. Once the query provides this the individual record can be deleted via an updateable view. IIRC, SQL Server is particularly good at this, making CTEs updateable! In fact, i specifically remeber seeing a query to remove duplicates with a PK, using this method.
Robert Ingmarsson wrote: I dont know how to write this query "delete value in the selected table row from the database based on its primary key " Remember i am a beginner to jdbc.
Brian Tkatch wrote:
Robert Ingmarsson wrote: I dont know how to write this query "delete value in the selected table row from the database based on its primary key " Remember i am a beginner to jdbc.
If you are "leaving" the database after getting the values, and want to "go back" and refer to one of the records, you must retrieve an identifier (as Paul explained), which in your case is the id column. While you do not need to display the id to your users, you do need to internally associate each record with that id. Once the user decides to delete a record, a standard DELETE can be issue for that record's id:
Robert Ingmarsson wrote:Thank you again! Now i am even more confused, but i take it as a good sign. Ok, so how do i associate each record in the database internally with that id?
Robert Ingmarsson wrote:Ok, so how do i associate each record in the database internally with that id?
You may have just won ten million dollars! Or, maybe a tiny ad.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
|