Reputation: 1631
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
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
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