Update only 1 row

  • Thread starter Thread starter ZippyV
  • Start date Start date
Z

ZippyV

I have to create a query that updates only 1 row in a table with no primary
keys defined and double records. The problem I'm having is that my WHERE
statement (in the UPDATE query) can give more than 1 result (because of the
double records). How can I make sure that only 1 of those records is
updated?
 
Unless you can come up with some way of uniquely identifying the records,
you can't.
 
Dear Zippy:

The reason for a primary key or other unique index is to give the rows of a
table an "identity." A row can have an identity without a primary key or
unique index, but the primary key or unique index is used to enforce
uniqueness, which is the essence of identity. Without uniqueness, choosing
one from from a set of more than one is simply not possible.

Your description leads me to believe that you could have pairs of rows that
are exact copies of one another. Updating one, and not the other, is
arbitrary and ambiguous. In the real world, which we attempt to model with
databases, this rarely makes any sense.

For example, if your update query replaces a certain column I'll call
Column1 using a certain set of criteria to identify the row, and a
subsequent update replaces Column2 using the same criteria, does it not
matter whether the two updates change the same row, or two different rows?

Queries are designed to avoid ambiguity and arbitrariness. What you
describe would be better done using a recordset in VBA. That is, assuming
what you describe is really worth doing at all.

Tom Ellison
 
I'll explain my problem in a bit more detail:
- I got one table that holds Albums (cd's, dvd's) and has a pk called
AlbumId
- I got another table that holds Locations (where the albums are stored) and
each location has a pk called LocationId
- I have a third table AlbumLocation that holds the LocationId and the
AlbumId for each album,
I used to have those 2 columns as pk in this table but this means I can't
have more than 1 of the same album in 1 location.
This is the reason why I have double records and can't have those 2 columns
as pk's.

If I would rent out one of those albums I would use an update query that
takes 1 of those records and changes it's LocationId to 1 (1 = rented out).
When an album is brought back I would take a row with the right AlbumId and
a LocationId of 1 and change the LocationId to a value given by the user.

How would you guys solve this problem? It's certainly possible in the real
world to have 2 albums right next to eachother at the same place. :-)
I could put those 2 pk's back in the AlbumLocation table and refuse 2 of the
same albums on the same location.
 
Dear Zippy:

The first thing that occurs to me is to have a Quantity column in the
AlbumLocation table. Rather than have 3 rows for 3 albumns, just have one
row with quantity 3. When the real-world situation contains multiple
indistinguishable items, this is the way to model it.

The suggestion I made earlier to use a recordset of cursor would be valid.

Tom Ellison
 
Thanks for the replies, I'll go with the quantity column solution. That
sounds like the best one.
 
Back
Top