Geek asks and answers

SQL: Is there a way to use Order By in an update statement?

I need to update one record only in the database and assign it to the user. That's what I'm doing:

UPDATE TOP (1) books SET assigneduser = 1
WHERE bookstatus = 7
AND ((assigneduser is null) or (assigneduser = 1));

I also have a field called bookname that I would prefer to order, but the update does not seem to support it.

Also note that I will have 50 users using the software right away, so I will need to make sure that only one user is assigned a book. Otherwise, I would first make a selection, and then update at the top entry.

Thank.

+3
source share
7 answers

You must first select the desired entry and then update it:

update books
    set assigneduser = 1
where BookPrimaryKeyField = (
    SELETE TOP 1 BookPrimaryKeyField
    from books
    WHERE bookstatus = 7
    AND ((assigneduser is null) or (assigneduser = 1));
)
+2
source

, - , . ,

UPDATE books SET assigneduser = 1
WHERE BOOKID 
= (SELECT top 1 BOOKID FROM books where
 bookstatus = 7
AND ((assigneduser is null) or (assigneduser = 1)));
+1

, " " , ORDER BY , , , , .

, , 1 .

UPDATE b0 SET assigneduser = 1
FROM b0 
  INNER JOIN
(SELECT top 1 id FROM books
WHERE user = 1 OR user is null
AND status =7)  b1 ON b1.id = b0.id

, ,

UPDATE user SET assigneduser = 1
WHERE id IN
(SELECT top 1 id FROM books
WHERE user = 1 OR user is null
AND status =7)  b1 ON b1.id = b0.id

concurrency, .

0
UPDATE B
SET assigneduser = 1
FROM books B
WHERE bookstatus = 7
AND ((assigneduser is null) or (assigneduser = 1))
and bookid = (select min(bookid) from books where assigneuser is null)

, .

, ( b), .

0

Take a look at http://msdn.microsoft.com/en-us/library/ms177523.aspx .

If you must use TOP to apply updates in a meaningful history, you must use TOP along with ORDER BY in the subquery statement. The following example updates the hours of 10 earliest employees.

UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee
     ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;
GO
0
source

It would probably be easier to break it into 2 parts

    DECLARE @bookid as INT

    SELECT TOP (1) @bookid = id FROM books
    WHERE bookstatus = 7
    AND ((assigneduser is null) or (assigneduser = 1))
    ORDER BY bookname

    UPDATE books SET assigneduser = 1
    WHERE id = @bookid
0
source

There is a way around it using a subquery, as shown below:

UPDATE books SET assigneduser=1
AND ((assigneduser is null) or (assigneduser = 1))
AND bookname in (SELECT TOP 1 bookname FROM Table ORDER BY bookname DESC)
0
source

More articles:


All Articles