How to Update from a Select in SQL Server

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'

SQL UPDATE SELECT with WHERE

You need to do a check on ClassName as well:

update b1
set b1.defaultguid =
    (
    select b2.defaultguid
    from cSC_BusinessUnit b2
    where b2.BusinessUnitGUID = 5
        AND b2.ClassName = b1.ClassName
    )
from cSC_BusinessUnit b1
where b1.BusinessUnitGUID = 7

SQL Update a Column From a Select

you do not need subquery, And use is null instead of = null

UPDATE L
SET loadStatus = 'SCHEDULED'
    From [Loads] L left join [Transaction] T on L.OID = T.IBLoad
    Where T.IBLoad is null and load_type = 1 
    and  L.loadStatus  is  null

or use Loads.loadStatus = '' for empty string

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'

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

SQL Server - UPDATE data based on SELECT

Under most circumstances, SQL updates are performed using direct references to a particular table (UPDATE books SET books.title = 'The Hobbit' WHERE books.id = 1). Yet, on occasion, it may prove beneficial to alter the contents of a table indirectly, by using a subset of data obtained from secondary query statement.

Performing an UPDATE using a secondary SELECT statement can be accomplished in one of two ways, primarily depending upon which version of SQL Server you are using. We’ll briefly explore both options so you can find what works best for you.

Using INNER JOINS

For all SQL Server installations, the most basic method of performing this action is to use an INNER JOIN, whereby values in the columns of two different tables are compared to one another.

UPDATE
  books
SET
  books.primary_author = authors.name
FROM
  books
INNER JOIN
  authors
ON
  books.author_id = authors.id
WHERE
  books.title = 'The Hobbit'

In the above example, we’re UPDATING the books.primary_author field to match the authors.name for ‘The Hobbit’ by JOINING both tables in the query to their respective, matching values of authors.id and books.author_id.

Using MERGE to UPDATE and INSERT Simultaneously

For SQL Server 2008 and newer, Microsoft introduced the exceptionally useful MERGE operation which is similar to the above INNER JOIN method, but MERGE attempts to perform both an UPDATE and an INSERT command together. This effectively synchronizes the two tables based on the query performed, updating and inserting records as necessary for the two to match.

MERGE INTO
  books
USING
  authors
ON
  books.author_id = authors.id
WHEN MATCHED THEN
  UPDATE SET
    books.primary_author = authors.name
WHEN NOT MATCHED THEN
  INSERT
    (books.author_id, books.primary_author)
  VALUES
    (authors.id, authors.name)

The full query when using MERGE is certainly a bit more complex then that of a basic INNER JOIN, but once you grasp how the operation functions, you’ll quickly understand how powerful this capability can truly be.

The first few lines are rather self-explanatory:

MERGE INTO
  books
USING
  authors
ON
  books.author_id = authors.id

We want to MERGE INTO (UPDATE/INSERT) the books table by using the secondary authors table, and we’re matching the two based on the same books.author_id = authors.id comparison.

Where the MERGE command differs is in the branching logic that follows.

WHEN MATCHED THEN
  UPDATE SET
    books.primary_author = authors.name

Here we’re asking SQL to perform an action only when records MATCHED – when an existing record is found. In that case, we perform a standard UPDATE just as we did before, setting the books.primary_author field to equal the authors.name field.

Finally, if the query discovers a matching comparative record that doesn’t exist, we instead perform an INSERT.

WHEN NOT MATCHED THEN
  INSERT
    (books.author_id, books.primary_author)
  VALUES
    (authors.id, authors.name)

Here we’re simply asking SQL to INSERT a new record into the books table and passing along the values for the author_id and primary_author fields, grabbed from the associated authors table record.

The end result of our MERGE statement is that for every author in the authors table, we verify whether a corresponding book exists in books. If a record is found, we ensure books.primary_author is set using UPDATE, and where no match is found, we add a new record to books.

With that, you should have a solid understanding of two different methods that can be used to UPDATE records in SQL by using secondary, comparative SELECT statements.

SQL UPDATE TOP () or UPDATE with SELECT TOP

First statement will be faster. But the top 150 records are chosen randomly. Records updated in both the queries might not be same. Since you are spitting the updates into batches your approach may not update all records.

I will do this using following consistent approach than your approach.

;WITH cte
     AS (SELECT TOP (350) value1,
                          value2,
                          value3
         FROM   database1
         WHERE  value1 = '123'
         ORDER  BY ID -- or any other column to order the result 
        )
UPDATE cte
SET    value1 = '',
       value2 = '',
       value3 = '' 

Also you don't have to worry transaction log size when updating couple thousands records there is no need of batches here

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 

SQL Server - UPDATE table where ID is in SELECT?

If you remove the exists you have a valid query from what I can tell.

UPDATE mytable 
   SET status = 'PM'
 WHERE id IN (select ID from ...)

Works for me in MySql 5.5, not sure which database you're using.

---

Related Topics