How to Select and Update Rows at the Same Time

Is there a way to SELECT and UPDATE rows at the same time?

Consider looking at the OUTPUT clause:

USE AdventureWorks2012;  
GO  

DECLARE @MyTableVar table(  
    EmpID int NOT NULL,  
    OldVacationHours int,  
    NewVacationHours int,  
    ModifiedDate datetime);  

UPDATE TOP (10) HumanResources.Employee  
SET VacationHours = VacationHours * 1.25,  
    ModifiedDate = GETDATE()   
OUTPUT inserted.BusinessEntityID,  
       deleted.VacationHours,  
       inserted.VacationHours,  
       inserted.ModifiedDate  
INTO @MyTableVar;  

--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate  
FROM @MyTableVar;  
GO  
--Display the result set of the table.  
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee;  
GO 

Select and Update in the same time?

It appears to be possible to use TOP in a subquery with Access in a WHERE clause. If so, then you can try using the following UPDATE statement:

UPDATE Table1
SET inProgress = True
WHERE ComputerName = (SELECT TOP 1 ComputerName FROM Table1 WHERE inProgress = False)

Note that using using TOP 1 without an ORDER BY clause does not make much sense, because you are providing no logic with regard to which of the matching records should be chosen first. You probably would want to add ORDER BY to the above subquery. Also note that, as I mentioned in the comments, it is not possible to update and select at the same time. If you really need the fields from your original SELECT, then you would need a separate query/statement for that. One option might to label the updates with a timestamp, and then select the most recently updated record after the fact.

sql how to select and update at the same time

You can use OUTPUT clause:

UPDATE dbo.tableName 
SET processed = 2 --or whatever you want..
OUTPUT inserted.*
WHERE processed = @processed

From MSDN (Link above):

Returns information from, or expressions based on, each row affected
by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be
returned to the processing application

How to select and update in one query?

Resolve this!

DECLARE @id int;
SET @id = (select top(1) id from [table] where [x] = 0 order by id desc);

select * from [table] where id = @id;
update [table] set [x] = 20 where id = @id;

:D

How do I UPDATE from a SELECT in SQL Server?

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

Update row with select on same table

You don't specify the database. The following is standard SQL:

UPDATE t
    SET TEXT = (SELECT text
                FROM t t2
                WHERE t.id = t2.id AND LANG ='EN' AND
                      TEXT IS NOT NULL
               ) 
    WHERE TEXT IS NULL;

In the event of duplicates, the following should work:

UPDATE t
    SET TEXT = (SELECT max(text)
                FROM t t2
                WHERE t.id = t2.id AND LANG ='EN' AND
                      TEXT IS NOT NULL
               ) 
    WHERE TEXT IS NULL;

EDIT:

Of course, not all databases support all ANSI standard functionality. In MySQL, you would use a join instead:

UPDATE t JOIN
       (SELECT id, max(text) as text_en
        FROM t t2
        WHERE LANG ='EN' AND TEXT IS NOT NULL
       ) ten 
       ON t.id = ten.id
    SET t.TEXT = ten.text_en
    WHERE t.TEXT IS NULL;

How to select and update selected rows in a single sql query?

As long as you are using SQL Server 2005 or later you can make use of OUTPUT in a single query.

UPDATE Car
SET    sent_to_server = 1 
OUTPUT Inserted.id, Inserted.plate
WHERE  sent_to_server = 0;

This will update the rows where sent_to_server is zero and return the modified rows. No need for a transaction.

SQL Fiddle

---

Related Topics