How to update my table where updated columns names stored in another table in SQL Server

Question

I have table User with n columns that stores user information in it.

I have another table User_Edit_Changes that I use to temporarily store changes to table User in it so that after admin confirmation I update the actual table User with new values.

In table User_Edit_Changes, I stored which user column requested for update and what is new value for that. How to write a dynamic query to get just changed value columns and new value from User_Edit_Changes and update the User table?

here is my sample create table command , teacher stores infos, Tbl_ProfessorRequest stores edit change request, Tbl_ProfessorEditInfoFields stores which fileds teacher request to edit

CREATE TABLE [dbo].[Teacher](
[code_ostad] [numeric](18, 0) NOT NULL,
[name] [varchar](30) NULL,
[family] [varchar](40) NOT NULL,
[namep] [varchar](30) NULL,
[idmadrak] [numeric](18, 0) NULL,
[namemadrak] [varchar](50) NULL,
[idresh] [numeric](18, 0) NULL,
[nameresh] [varchar](50) NULL,
[martabeh] [numeric](18, 0) NULL,
[namemartabeh] [varchar](30) NULL,
[nahveh_hamk] [numeric](18, 0) NULL,


CREATE TABLE [Request].[Tbl_ProfessorRequest](
[ProfessorRequestID] [int] IDENTITY(1,1) NOT NULL,
[Code_Ostad] [int] NULL,
[RequestTypeID] [bigint] NULL,
[RequestLogID] [bigint] NULL,
[CreateDate] [nvarchar](10) NULL,
[Note] [nvarchar](1000) NULL,
[term] [nvarchar](8) NULL,
[ProfessorMessage] [nvarchar](1000) NULL,
[Erae_Be] [nvarchar](100) NULL,
[ChangeSet] [int] NULL,
[isdeleted] [bit] NOT NULL,
[ScanImageUrl] [nvarchar](300) NULL,




 CREATE TABLE [Request].[Tbl_ProfessorEditInfoFields](
[Id] [int] IDENTITY(1,1) NOT NULL,
[code_ostad] [int] NOT NULL,
[teacher_Column_Name] [nvarchar](200) NULL,
[OldValue] [nvarchar](200) NULL,
[NewValue] [nvarchar](200) NULL,
[State] [int] NOT NULL,
[ProfessorRequestID] [int] NOT NULL,

The same way you would do a regular update query, build it in a string and then execute the string. — Chuck, Jun 12 '16 at 06:36

James Z · Answer 1 · 2016-06-12T10:51:00.240

I'd say you have 3 options:

Handle the logic of updates outside the database, in what ever your application is built with. That's most likely the easiest way, since this kind of dynamic handling is not what databases are good at.

Build a dynamic SQL clause based on the contents of User_Edit_Changes. Loop through the changes in the table, construct an update statement into a variable and use sp_executesql to execute it. With cursor the code should be something like this:

set @params = N'@NewValue varchar(100)'

fetch next from yourcursor into @FieldName, @NewValue

while @@FETCH_STATUS = 0 begin    
  set @sql = 'update User set ' + @FieldName + ' = @NewValue'
  exec sp_executesql @sql, @params, @NewValue = @NewValue  

  fetch next from yourcursor into @FieldName, @NewValue

end

Create static SQL statements for updating each of the columns. You can build something like this:
```
update U
set U.UserName = C.NewValue
from
  User U 
  join User_Edit_Changes C on U.UserId = C.UserId
where
  C.FieldName = 'UserName'
```
For this you of course need to have similar statements for each of your columns. You could build one massive update query with pivot or max+case, but handling the old and new values gets pretty complex.

thanks james , i want to do it in second approach you told by dynamic query , but unfortunately i don't know how . — M Taher, Jun 12 '16 at 07:02
@MTaher I added basic idea how to do the dynamic part inside a cursor — James Z, Jun 12 '16 at 10:51

How to update my table where updated columns names stored in another table in SQL Server

1 Answers1