CODEWITHSUNDEEP
c#mysql
ChampChris
ChampChris

Reputation: 1631

Update with custom values

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...)**;

Upvotes: 0

Views: 50

Answers (2)

Valuator
Valuator

Reputation: 3637

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();
}
}

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133410

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()

Upvotes: 0

Related Questions