Update first row in SQL using LIMIT

Question

I'm having a trouble with something that looks like simple thing. I'm trying to find first row that satisfies WHERE part of query and UPDATE it.

UPDATE Donation SET Available=0 WHERE Available != 0 and BloodGroup='" + bloodGroup + "' LIMIT 1"

bloodGroup is variable that gets filled automatically using C# and it keeps string value of selected blood group.

When I try to run this I get incorrect syntax near 'limit'.

What I'm doing wrong? Is it possible using LIMIT like during UPDATE query?

During debugging I got query like this:

UPDATE Donation SET Available=0 WHERE Available != 0 AND BloodGroup='AB-' LIMIT 1

MySQL allows limit in update queries, when you don't have a join. The syntax looks okay. — Gordon Linoff
– Gordon Linoff, Commented Sep 4, 2014 at 11:45
Are you sure you are connected to MySQL? Not all databases support limit? C# is often used with SQL Server. — Gordon Linoff
– Gordon Linoff, Commented Sep 4, 2014 at 11:50
Gordon Linoff is right. Your query is okay. What about using double quotes for string? like "AB-". — Redone
– Redone, Commented Sep 4, 2014 at 11:51
Yep u both were rigth...I guess I was little stupid today and didn't use my brain in a proper way :P Thanks guys for helping, problem solved :) — nemo_87
– nemo_87, Commented Sep 4, 2014 at 12:02

Gordon Linoff · Accepted Answer · 2014-09-04 11:53:14Z

Because C# is often used with SQL Server, perhaps the question is mistagged. The syntax looks fine for MySQL.

In SQL Server, you can do this as:

UPDATE TOP (1) Donation SET Available = 0 WHERE Available <> 0 AND BloodGroup = 'AB-';

Note that this chooses an arbitrary matching row, as does your original query (there is no order by).

That was a problem. I wasn't thinking clear. Trying to use MySQL and totally forgot that VS uses SQL Server that doesn't support limit like that. Thank u very much, this totally worked! :)

Geo V L · Accepted Answer · 2014-09-04 11:59:47Z

It is not safe to use limit in update queries. Please refer http://bugs.mysql.com/bug.php?id=42415

The documentation states that any UPDATE statement with LIMIT clause is considered unsafe since the order of the rows affected is not defined: http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html

However, if "ORDER BY PK" is used, the order of rows is defined and such a statement could be logged in statement format without any warning.

You can use like this way limit in Update Queries like these

 UPDATE messages SET test_read=1 WHERE id IN ( SELECT id FROM ( SELECT id FROM messages ORDER BY date_added DESC LIMIT 5, 5 ) tmp );

Also please

thanks for answering. You are right, I've also heard that it's not save using LIMIT during UPDATE, but didn't know how to solve problem. There real thing was much simple...I was stupid enough to write query in MySQL, totally forgetting that VS uses SQL Sever by default. :)

Punitha Subramani · Accepted Answer · 2014-09-04 12:00:35Z

Can you try it? way of getting row_number

UPDATE Donation d1 join (SELECT id,(SELECT @Row:=0) as row,(@Row := @Row + 1) AS row_number FROM Donation where Available <> 0 AND BloodGroup='AB-') d2 ON d1.id=d2.id SET d1.Available='three' WHERE d1.Available <> 0 AND d1.BloodGroup='AB-' AND d2.row_number='1'

Collectives™ on Stack Overflow

Update first row in SQL using LIMIT

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related