How to Write Update SQL with Table Alias in SQL Server 2008

How to write UPDATE SQL with Table alias in SQL Server 2008?

The syntax for using an alias in an update statement on SQL Server is as follows:

UPDATE Q
SET Q.TITLE = 'TEST'
FROM HOLD_TABLE Q
WHERE Q.ID = 101;

The alias should not be necessary here though.

SQL Server: how to use alias in update statement?

SQL Server supports updates using joins.

This means you can write your query like this:

UPDATE s
SET Value = d.NumOfRows
FROM statisticsTable s
INNER JOIN
(
     SELECT UserId, COUNT(*) As NumOfRows
     FROM OtherTable
     GROUP BY UserId
) d ON s.UserId = d.UserId
WHERE id in (1,2,3) 

How to write UPDATE from XML with Table Alias in SQL Server 2008 already have a 'From' statement

Try this,

UPDATE T SET 
T.PrintedBy = ISNULL(T.PrintedBy,der.PrintedBy), 
T.PrintedDate = ISNULL(T.PrintedDate,GETDATE()),
@RetVal=der.NoteId 
FROM (     
    SELECT PrintedBy,NoteId
    FROM OPENXML(@hDoc1,'TableName',1)
    WITH   (  PrintedBy INT 'PrintedBy', 
              NoteId INT 'NoteId'
            )
     ) as der, TableName T
WHERE T.NoteId = der.NoteId 

How to alias a table while updating the same table from itself?

How about this:

UPDATE a
SET a.adusername = x.adusername 
FROM Accounts x
INNER JOIN Accounts a ON x.AccountName = a.rev
WHERE x.ok>10
    AND a.ok in (0,1,2,3) 
    AND x.ADUserName is not null
    AND a.ADUserName is null

Update table from a table while joining on a column alias

You don't need to alias in the update clause(s). e.g. try:

update d
set d.Latitude = t.latitude, d.Longitude = t.longitude
FROM tblBoxAddress d inner join 
tblUpdatedData t on t.Address = d.Address + ' ' + d.zip;

Update not working using alias and nested select

SQL Server does not allow aliases in the update. So, you could just use the full table name:

update BranchTagging 
    set Date_End = '2021-12-31'
where Branch_ID in (select ft.Branch_ID
                    from ##ForTagging2021 ft
                    where ft.code = BranchTagging.Code and
                          ft.Source_Tagging = BranchTagging.Source_Tagging
                   );

It seems a little odd to me to use in with a correlated subquery. More typically, I would just use exists.

Also, you can assign aliases if you use a from clause, which would be an alternative way to formulate the query. The above, though, is standard SQL.

Update statement is giving error for table alias

This is the correct syntax:

alter table #table1 add C21 datetime;
update a
set a.C21 = (
  select b.C21_result_lab from #table2 b 
  where 
    a.person_id = b.person_id 
    and 
    a.source_id = b.source_id
)
from #table1 a

You must make sure that the select query does not return more than 1 rows.

Or you can do it with a join:

update a
set a.C21 = b.C21_result_lab 
from #table1 a inner join #table2 b 
on a.person_id = b.person_id and a.source_id = b.source_id

---

Related Topics