10 Replies - 538 Views - Last Post: 17 July 2019 - 07:20 AM Rate Topic: -----

#1 dako   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 60
  • Joined: 01-July 19

Deleting records from sqlite table by name rather than row id

Posted 15 July 2019 - 09:31 PM

So maybe this syntax isn't perfect but I know how to use delete * from "table" where id = "id_number";

But what if I have a field titled username and I want to do something like delete all from "table" where username = "name" ? I tried it with a mimicked syntax of the id and it would not work. If someone could shed some light it would be appreciated!
Is This A Good Question/Topic? 0
  • +

Replies To: Deleting records from sqlite table by name rather than row id

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,898
  • Joined: 12-June 08

Re: Deleting records from sqlite table by name rather than row id

Posted 15 July 2019 - 09:40 PM

Please post your current code.
Was This Post Helpful? 0
  • +
  • -

#3 dako   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 60
  • Joined: 01-July 19

Re: Deleting records from sqlite table by name rather than row id

Posted 15 July 2019 - 09:42 PM

View Postmodi123_1, on 15 July 2019 - 09:40 PM, said:

Please post your current code.




public void removeData() {
		 try {
			 Statement input2 = conn.createStatement();
			 Statement inputNames = conn.createStatement();
			 
			 
			 ResultSet rsIn2 = inputNames.executeQuery("select * from sample where id = " + recordCount + " ;");
			 while(rsIn2.next()) {
				 int id = rsIn2.getInt("id");
				 String username = rsIn2.getString("name");
				 String password = rsIn2.getString("password");
				 
				 
				 System.out.println(id + " " + username + " " + password);
				 JOptionPane.showMessageDialog(null, "Deleted " + username + " " + password );
			 } 
			 
			 
			 rsIn2 = input2.executeQuery("delete from sample where id = " + recordCount + " ;");			 
			 
		} 


Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,898
  • Joined: 12-June 08

Re: Deleting records from sqlite table by name rather than row id

Posted 15 July 2019 - 09:44 PM

Why would an ID be 'record count'? Those are not two similar things. One is a specific label and the other is a value of how many of something you have.

Where is your DELETE with a user name?

IDs exist so you do not have to try and kludge string comparison.. with correct casing, spacing, character encoding, etc.
Was This Post Helpful? 0
  • +
  • -

#5 dako   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 60
  • Joined: 01-July 19

Re: Deleting records from sqlite table by name rather than row id

Posted 15 July 2019 - 09:58 PM

View Postmodi123_1, on 15 July 2019 - 09:44 PM, said:

Why would an ID be 'record count'? Those are not two similar things. One is a specific label and the other is a value of how many of something you have.

Where is your DELETE with a user name?

IDs exist so you do not have to try and kludge string comparison.. with correct casing, spacing, character encoding, etc.



Record count is simply an integer I created that gets incremented when a new record is added so that when I press delete it will delete the most recently added record. I did this because the id is a primary key and is set to auto increment... It isn't like a built in member to some class I am using in the java library.

I have not tried to use the username query or at least I haven't included it because when I tried it yesterday it did not work. I was hoping someone could tell me the appropriate syntax.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,898
  • Joined: 12-June 08

Re: Deleting records from sqlite table by name rather than row id

Posted 16 July 2019 - 05:19 AM

Try it again, post what you tried, and any error message s.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,184
  • Joined: 12-December 12

Re: Deleting records from sqlite table by name rather than row id

Posted 17 July 2019 - 03:30 AM

View Postdako, on 16 July 2019 - 04:58 AM, said:

I have not tried to use the username query or at least I haven't included it because when I tried it yesterday it did not work. I was hoping someone could tell me the appropriate syntax.

The syntax is the same as deleting based on a numeric value, except that a text value needs to be delimited with single quotes. BUT, use parameters to provide the value(s) - to avoid SQL injection and avoid issues with, for example, embedded quotes.

As mentioned, if you have tried it then you should post code and details of the failed attempt, together with any error details.
Was This Post Helpful? 0
  • +
  • -

#8 dako   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 60
  • Joined: 01-July 19

Re: Deleting records from sqlite table by name rather than row id

Posted 17 July 2019 - 06:28 AM

 	 public void removeData(String nameIn) {
		 try {
			 Statement input2 = conn.createStatement();
			 Statement inputNames = conn.createStatement();
			 
			 
			 ResultSet rsIn2 = inputNames.executeQuery("select * from sample where name = " + nameIn + " ;");
			 while(rsIn2.next()) {
				 int id = rsIn2.getInt("id");
				 String username = rsIn2.getString("name");
				 String password = rsIn2.getString("password");
				 
				 
				 System.out.println(id + " " + username + " " + password);
				 JOptionPane.showMessageDialog(null, "Deleted " + username + " " + password );
			 } 
			 
			 
			 rsIn2 = input2.executeQuery("delete from sample where name = " + nameIn + " ;");			 
			 
		} 
		 catch (Exception e) {
			// TODO: handle exception
		 }
	 } 


There is no error. This is exactly what I tried before and it just doesn't do anything.
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,898
  • Joined: 12-June 08

Re: Deleting records from sqlite table by name rather than row id

Posted 17 July 2019 - 07:02 AM

19        rsIn2 = input2.executeQuery("delete from sample where name = " + nameIn + " ;");  


That's what I figured.. read up on datatypes in SQL. What does that string look like?

delete from sample where name = someText;

Does 'someText' look like a text value, or a column name? Yes - it looks like a column name.. similar to the left hand side where you use column 'name'.

So.. how does one go about indicating text values? Flipping through any sql and tick marks indicate to sql server that the value is text and not a column name.

Going a step further it is *BAD* practice to just append anything all willy nilly into your SQL statements. This is 101 issue for folks breaking into databases and ransacking the data, deleting it, or modifying it outside of normal use. This is why you should be using PARAMETERS. Parameterized queries help remove the security risk and format things correctly for the query per your defined intended use.
Was This Post Helpful? 0
  • +
  • -

#10 dako   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 60
  • Joined: 01-July 19

Re: Deleting records from sqlite table by name rather than row id

Posted 17 July 2019 - 07:11 AM

View Postmodi123_1, on 17 July 2019 - 07:02 AM, said:

19        rsIn2 = input2.executeQuery("delete from sample where name = " + nameIn + " ;");  


That's what I figured.. read up on datatypes in SQL. What does that string look like?

delete from sample where name = someText;

Does 'someText' look like a text value, or a column name? Yes - it looks like a column name.. similar to the left hand side where you use column 'name'.

So.. how does one go about indicating text values? Flipping through any sql and tick marks indicate to sql server that the value is text and not a column name.

Going a step further it is *BAD* practice to just append anything all willy nilly into your SQL statements. This is 101 issue for folks breaking into databases and ransacking the data, deleting it, or modifying it outside of normal use. This is why you should be using PARAMETERS. Parameterized queries help remove the security risk and format things correctly for the query per your defined intended use.



Okay so do you care to explain how to make parametric my query? Because I don't know and just telling me it is foolish to not do so when, based on the fact that I'm not doing it, I obviously don't know how to even begin doing so.
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,898
  • Joined: 12-June 08

Re: Deleting records from sqlite table by name rather than row id

Posted 17 July 2019 - 07:20 AM

Perhaps take the terms and do a cursory search and look into it.

I suggest terms like:

"sqlite parameterized query"

or swap out 'sqlite' with what ever language your code is currently in. :^:
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1