Update a Single Row with T-Sql

Update a single row with t-sql

Often tables have a unique ID. And you should filter on that.

For example,

UPDATE YourTable
SET YourColumnToUpdate = 'your_value'
WHERE YourUniqueColumn = @Id

If your table does not have a unique ID, consider adding one: an integer column with a Primary Key and Identity.

How to update only one row in a table?

you can use ROWCOUNT

SET ROWCOUNT 1

UPDATE table1 
SET name2 = '01' 
WHERE name1='xx'

SET ROWCOUNT 0

or you can use update top

UPDATE TOP (1) table1 
SET name2 = '01' 
WHERE name1='xx'

SQL Update only updates one row

First of all let's reduce the eye hurting SQL to what it really is:

update sde.sy1.valve_evw
set ma = (select mc from sde.sy1.valve_join_evw)
where primarykey in (select primarykey from sde.sy1.valve_join_evw where ma <> mc)

WHERE clause

We look for all primarykey in valve_join_evw where a record's ma <> mc. We update all valve_evw records with such primarykey.

SET clause

For a record we want to update, we set ma to the value found with:

select mc from sde.sy1.valve_join_evw

But this query has no where clause, so what value does it select to fill the record's ma field? It selects all mc from valve_join_evw, so the DBMS probably picks one of these values arbitrarily. (It would be better, it raised an error.)

Conclusion

It is very easy to see which records the statement will update.

Which primarykey:

select primarykey from sde.sy1.valve_join_evw where ma <> mc

Which rows:

select * 
from sde.sy1.valve_evw
where primarykey in (select primarykey from sde.sy1.valve_join_evw where ma <> mc)

As to the SET clause: Add a WHERE clause to your subquery that relates the record to select to the record to update (same ma? same primarykey?) E.g.:

set ma = 
(
  select mc 
  from sde.sy1.valve_join_evw vj
  where vj.primarykey = valve_evw.primarykey
    and vj.ma         = valve_evw.ma
)

SQL update one single row table with data from another

You can use a CTE with a ROW_NUMBER() function to do this. Simple example:

DECLARE @TABLE AS TABLE (Testing INT, Testing2 VARCHAR(55), Testing3 BIT);
INSERT INTO @TABLE VALUES (1, '1', 1);
INSERT INTO @TABLE VALUES (1, '1', 1);
INSERT INTO @TABLE VALUES (1, '1', 1);
INSERT INTO @TABLE VALUES (1, '1', 1);
INSERT INTO @TABLE VALUES (1, '1', 1);
INSERT INTO @TABLE VALUES (1, '1', 1);
INSERT INTO @TABLE VALUES (1, '1', 1);
INSERT INTO @TABLE VALUES (1, '1', 1);

WITH CTE AS 
(
    SELECT
         ROW_NUMBER() OVER (ORDER BY Testing) AS RowID
        ,Testing
        ,Testing2
        ,Testing3
    FROM @TABLE
)
UPDATE CTE
SET Testing = 2, Testing2 = '2', Testing3 = 0
WHERE RowID = 1
;
SELECT * FROM @TABLE
;

SQL Update single row, Where clause

I like to write SQL scripts that are idempotent:

UPDATE Users 
SET Enabled=1 
WHERE Id=1 
  AND Enabled<>1;   -- if column is defined as NOT NULL

Scenarios:

• trigger that writes to shadow/history table (it will fire only once)
• last update date (will change only once)
• change tracking will not mark row as updated
• maybe slower but more consistent

Is update/select of a single column in a single row atomic and safe in SQL Server?

For the table structure you have shown both the UPDATE and the SELECT are standalone transactions and can use clustered index seeks to do their work without needing to read unnecessary rows and take unnecessary locks so I would not be particularly concerned about deadlocks with this procedure.

I would be more concerned about the fact that you don't have the UPDATE and SELECT inside the same transaction. So the X lock on the row will be released as soon as the update statement finishes and it will be possible for another transaction to change the column value (or even delete the whole row) before the SELECT is executed.

If you execute both statements inside the same transaction then I still wouldn't be concerned about deadlock potential as the exclusive lock is taken first (it would be a different matter if the SELECT happened before the UPDATE)

You can also address the concurrency issue by getting rid of the SELECT entirely and using the OUTPUT clause to return the post-update value to the client.

UPDATE Test SET Column1 = Column1 & ~2 
OUTPUT INSERTED.Column1
WHERE Id = 1

Can't update only 1 row. How to change 1 row?

You need just a slight, provided that your DB's version is at least 12c, change by replacing the part WHERE Position IN (SELECT Position with WHERE ID IN (SELECT ID as having a primary column. The current case has no sense, since all testers will be converted to managers even replacing the IN <subquery> with ='Tester'. So use the following update statement by removing double quotes

UPDATE Schedule
   SET Position = 'Manager'
 WHERE ID IN
       (SELECT ID
          FROM Schedule
         WHERE Position = 'Tester'
         FETCH NEXT 1 ROW ONLY);

Demo

How to update a single row from multiple rows with UPDATE JOIN

One method is pre-aggregation:

update u
    set date_role_assigned = coalesce(user_role, u.date_role_assigned)
u.date_role_assigned),
        date_activated = coalesce(a.user_account_activated, u.date_activated),
        date_deactivated = coalesce(a.user_account_activated, u.date_deactivated)
from dbo.[User] u join
     (select a.entid,
             max(case when a.event_name = 'USER_ROLE_CHANGED' then a.event_date end) as user_role,
             max(case when a.event_name = 'USER_ACCOUNT_ACTIVATED' then a.event_date end) as user_account_activated,
             max(case when a.event_name = 'USER_ACCOUNT_DEACTIVATED' then a.event_date end) as user_account_deactivated
      from dbo.AuditEventsPivot a 
      group by a.entid
     ) a
     on a.entid = u.empid;

Updating Single Row per Group

You can define the row to update by using row_number() in a CTE. This identifies the first row in the group for the update:

with toupdate as (
      select tt.*, row_number() over (partition by OrderNumber order by id) as seqnum
      from #tempTable tt
     )
UPDATE toupdate
    SET toupdate.guestCount = q.guestCount
    FROM toupdate
    INNER JOIN queryTable q
    ON temp.OrderNumber = q.OrderNumber
    where toupdate.seqnum = 1;

The problem with you query is that temp is based on an aggregation subquery. Such a subquery is not updatable, because it does not have a 1-1 relationship with the rows of the original query. Using the CTE with row_number() is updatable. In addition, your set statement uses the table alias cc which is not defined in the query.

---

Related Topics