Database record deletion help

Hi I have a small problem here. Imagine I have a database table with three columns (userID, urlName, urlAddress). Imagine the values in these columns are "aa, bb, cc".

Now imagine I have some information like this sotred in the table:

userID urlName urlAddress
-------------------------------------------
aa bb cc
aa bb cc
aa bb cc
aa bb cc
aa bb cc
xx bb cc
zz bb cc

Now imagine that I wanted to delete urlName=bb where userID=aa....but not have it delete all 5 records with the same info in them?

many thanks

huh? you have to have some other filter than that if you dont want to delete all records.

SQL wise... terrible, no good, you'd need to set up a cursor and delete one row specifically, i think you might be able to do it that way... not sure

way 2 which is much easier, downloda them all to a disconnected record set, delete one row from the record set, and then reupload it, yaay, one row deleted!!

As roninblade said, you need to add another filter to your query, perhaps adding the UNIQUEID, you should *ALWAYS* have a uniqueID field inside your database so for example:


uniqueId userID urlName urlAddress
-------------------------------------------
1 aa bb cc
2 aa bb cc
3 aa bb cc
4 aa bb cc
5 aa bb cc
6 xx bb cc
7 zz bb cc


Then your query should look something like:

DELETE * FROM Table WHERE uniqueId = 1

You can also add more criterias such as:

AND urlName=bb AND userID=aa


Another way is to DELETE based on the result of a SELECT
Since you don't want to DELETE *all* the records where urlName=bb AND userID=aa then use the TOP(1) keyword if you'd like!

Hope this helps!
Sincerely

Vlince

that's not true at all, you guys are looking from a TSQL point of view only, you can do this with the methods i described *shrug* but what do i know, i did this a couple days ago, maybe they reorged the way Record Sets work within the past 2 days.

What do you mean by *that's not true at all* ?

Are you telling me my suggestion of creating a UNIQUEID for each row is *BAD* ???

And that using that UNIQUEID to delete the row is *BAD* ???

How so?

In fact, the poster's database design *IS* the problem, if it was design the proper way, he wouldn't have posted that message knowing that he could've used the UNIQUEID to delete the specific row he wanted no?

And how is adding a UNIQUEID to your TABLE a "looking from a TSQL point of view " that's nonsense! its simple database design no?

And on top of that, we are in the ASP forum NOT the ASP.NET one so the idea/concept of a "disconnected recordset" only applies to ADO.NET no?

You idea would work of course!
but why not simply add a UNIQUEID field in the TABLE and Query on it, who knows, he might need it in the long run for reports and what not?

Anyway its just a thought!

The more suggestion we have the better choice the poster have...that's my thinking

Hope this helps!
Sincerely

Vlince

hahaha, do i think good database design is bad? uhh... no, but since i work on a team with MIS majors i've quickly learned that you need to work around other people's deficiencies (they need to work around my complete lack of business knowledge, I work around their bad programming/database schema definitions).

But nope, DRS work in ASP too, i use it for a datagrid display class I use frequently to display large amounts of table info, that way you don't have to requery the db everyfriggin time you sort by column name or go to next page. But yep, it's in plain old asp, and the cursor thing can still work too =P For disconnected applications, ADO.NET offers performance advantages over ADO disconnected record sets. Whereas ADO requires that transmitting and receiving components be COM objects, ADO.NET transmits data in standard XML format so that COM marshaling or data-type conversions are not required.


I agree your method is a most fantastic method, but perhaps our friend does not have access to DDL and only DML. That is the corporate world, no one has the power they need =(

OK I see

Never used a DRS in ASP I use GetRows() which is faster...I think

But thanks I'll remember that!

Sincerely

Vlince

using drs or not; if that db table is all that is of it, it really needs to be modified for a primary key or some other unique field. the same data in all those rows is just poor design imo.

yep, agreed. I'm not undershooting what you know here but if one doesn't have access to DDL they cannot alter a primary key or add another column to uniquely identify a row. This is what we would call, bad database design, and I'm offering a way around it *shrug*