Update with custom values

Question

I have code that runs in c#, what this code does:

Loop through a list of users and update the rows in a table.

UPDATE CustomerUser 
                SET
                    LoginName = @LoginName,
                    UserName = @UserName,    
                    IsActive = @IsActive, 
                    IsDeleted = @IsDeleted,
                    DeletedDate = @DeletedDate,
                    Modified = CURRENT_TIMESTAMP()
                WHERE ID = @CustomerUserID;

in the c# code, it then does some concat'ing to the LoginName and userName columns.

I would like to replace that slow C# process with a SQL Statement that does something like what I have below. I know enough about SQL to think what I am asking might completely crazy, but never know till you ask.

UPDATE CustomerUser 
                    SET
                        LoginName = select concat( login,uniqueid) from CustomerUser where id **this would be an ID that is in the IN() of parent query**
                        UserName = @UserName,    
                        IsActive = @IsActive, 
                        IsDeleted = @IsDeleted,
                        DeletedDate = @DeletedDate,
                        Modified = CURRENT_TIMESTAMP()
                    WHERE ID **IN(1,2,3,4, etc...)**;

please update you question with a proper data sample and the expected result — ScaisEdge, Apr 24 '17 at 16:31

score 1 · Accepted Answer · edited May 23 '17 at 12:34

You can change your query to remove the SELECT subquery and just do:

UPDATE CustomerUser
SET LoginName = CONCAT(LoginName, UserName)
-- Other fields, etc..
WHERE ID **IN(1,2,3,4, etc...)**;

If you're doing this in C# I would recommend using Entity Framework rather than SQL Query strings. I've only used it with SQL Server, but info on using it with MySQL is discussed here:

Using MySQL with Entity Framework

With Entity, you could accomplish this by writing a function with a list of all ids to update as a parmeter:

void UpdateCustomerUsers(ICollection<int> ids)
{
using (var context = new MyDbContext())
{
    var customerUsers = context.CustomerUsers.Where(cu => ids.Contains(cu.ID));

    foreach (var cu in customerUsers)
    {
        cu.LoginName = cu.LoginName + cu.UserName;
        cu.Modified = DateTime.Now;
        // and so on...
    }

    context.SaveChanges();
}
}

not sure y the developer who did this chose raw inline SQL, but I want to move to or just update the inline SQL for now, this entire process is already slated for re-write, but performance needs to improved now. — ChampChris, Apr 24 '17 at 18:34

score 0 · Answer 2 · answered Apr 24 '17 at 16:35

You can use update with join

  UPDATE CustomerUser   as t1
  INNER JOIN  (  
           id from CustomerUser WHERE ID IN(1,2,3,4, etc...)
  ) t2  on t1.ID=t2.id
  SET
      LoginName = select concat( t1.login, t1.uniqueid) 
      UserName = @UserName,    
      IsActive = @IsActive, 
      IsDeleted = @IsDeleted,
      DeletedDate = @DeletedDate,
      Modified = CURRENT_TIMESTAMP()

Update with custom values

2 Answers2