UPDATE deficiency

Anon

Lets assume a table (not made by me) is malformed and I have no way of correcting it. By malformed I mean that one row can be exactly the same as another.

Now I want to update ONE of the rows. From what I can gather, there is no way to do this based on row number.

For SELECT statements, I can add an optional argument to LIMIT to achieve this, but there is no such thing for UPDATE.

Has anyone else solved this problem?

Thanks,
Sam

brandonschnell

if you're talking about mysql, rtfm.

according to the docs, mysql version 3.23 supports LIMIT on UPDATE queries.

bfranco

If didn't missunderstood what you said, you mean that you have duplicated rows in your table... and of course you don't have a primary key...

It's a mess, the last thing I would worry about is the UPDATE statement.

If you're using PostgreSQL, you can still use the OID field in the condition of your UPDATE. But I still don't see the point, because your data is not consistant. First, kill the database designer, and then you should worry bout the UPDATEs... 😛

Anon

While there IS a LIMIT, it does not have the optional parameter.

brandonschnell

http://www.mysql.com/doc/U/P/UPDATE.html

In MySQL Version 3.23, you can use LIMIT # to ensure that only a given number of rows are changed.

so yes, it has the parameter you need. run the update using a WHERE to select just the duplicate rows and use a "limit 1" and only one of the duplicate rows will change. repeat as necessary.

this isn't an UPDATE deficiency. like the other guy says, this is a deficiency with your database designer.

Anon

The only thing about that is the row that is changed would have to be the first occurance.

That doesn't really matter since it does the same thing, but since my client is GUI, it would be bad if the user changed the second occurance and the first one was actually altered.

If anyone complains, I'll tell them to fix their crappy table 😉

Thanks,
Sam