0
Update table1 set status='open' where user='test' order by id DESC

i want to update the last row with a where conditon

The
  • 13
  • 3
  • 1
    Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Mar 09 '21 at 06:18
  • Does this answer your question? [Update query using Subquery in Sql Server](https://stackoverflow.com/questions/14618703/update-query-using-subquery-in-sql-server) – samuei Mar 09 '21 at 06:18

3 Answers3

0

You can use window function row_number() to get the first row in descending order of id.

with cte as 
(
    select *,row_number()over(order by id desc)rn from table1 
)
Update cte set status='open' where user='test' and rn=1

Or you can use subquery to achieve same result:

UPDATE table1 SET status='open'
WHERE ID=(SELECT MAX(ID)FROM table1 where user='test') and user='test'
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
0

In order to find the record you want, you must first find the record you want and then change that record in the table. To do this, you need to create a temporary table and put the desired record in it, then update the table using the information obtained.

--Container to Insert Id which are to be iterated
Declare @temp1 Table
(
  ID int
)
--Container to Insert records in the inner select for final output

Insert into @temp1 

SELECT top 1 t.id FROM table1 t
WHERE t.user = 'test'
order by t.id desc

-- Keep track of @temp1 record processing
Declare @columnID int
Declare @columnValue varchar(100)

Begin

Set @columnID=(Select Top 1 id From @temp1)
Set @columnValue = 'open'
UPDATE table1 SET status = @columnValue WHERE id = @columnID


Delete @temp1 Where ID=@columnID
End
Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17
0

This should work, presume DBMS is MySQL

UPDATE table1
SET status = "open"
WHERE id in
    (SELECT *
     FROM
       (SELECT id
        FROM table1
        WHERE USER = "test"
        ORDER BY id DESC
        LIMIT 1) tmp_tbl);
hoangnh
  • 249
  • 4
  • 13