Updating Multiple Rows, Depending on Value, Using C# DBContext, and native Query-CodePudding

public class Selecter
{
    public string SelecterId { get; set; }
    public string UniqueText { get; set; } 
    public string OutputError { get; set; }
    public bool Selected { get; set; }
}

modelBuilder.Entity("WebAssistantClient.Models.Selecter", b =>
    {
        b.Property<string>("SelecterId").HasColumnType("nvarchar(450)");
        b.Property<string>("OutputError").HasColumnType("nvarchar(max)");
        b.Property<bool>("Selected").HasColumnType("bit");
        b.Property<string>("UniqueText").HasColumnType("nvarchar(max)");
        b.HasKey("TestSelecterId");
        b.ToTable("TestSelecters");
    }
);

CREATE TABLE [dbo].[Selecters](
    [SelecterId] [nvarchar](450) NOT NULL,
    [UniqueText] [nvarchar](max) NULL,
    [OutputError] [nvarchar](max) NULL,
    [Selected] [bit](max) NULL,
    CONSTRAINT [PK_Selecters] PRIMARY KEY CLUSTERED ([SelecterId] ASC)
)

Suppose that is my initial data:

SelecterId  UniqueText  Selected
"fruit0"    "apple"     false
"fruit1"    "banana"    true
"fruit2"    "lemon"     false
"fruit3"    ""          false

Requirement 1

I need when I set Selected column to true for some row, but changes the Selected column to false to other rows: Example: for "fruit0" Selected to true, the other rows in their colum Selected will be false, in this case the "fruit1"

SelecterId  UniqueText  Selected
"fruit0"    "apple"     true
"fruit1"    "banana"    false
"fruit2"    "lemon"     false
"fruit3"    ""          false

Requirement 2

I need when I set UniqueText column to value for some row, but changes the another row UniqueText column to "" (empty) if contains the same value: Example: for "fruit3" UniqueText to "lemon", the another row with same value become to empty "", in this case the "fruit2"

SelecterId  UniqueText  Selected
"fruit0"    "apple"     true
"fruit1"    "banana"    false
"fruit2"    ""          false
"fruit3"    "lemon"     false

Using SQL Server, Is it possible to do the requirements in one single sentence?

Using C#, How to do that using context.Selecter.FromSqlRaw("???");

CodePudding user response：

Using SQL Server, Is it possible to do the requirements in one single sentence?

You select both/all that you're interested in and then use a conditional set to work out what value to apply

Example: for "fruit0" Selected to true, the other rows in their colum Selected will be false, in this case the "fruit1"

UPDATE fruits 
SET Selected = CASE WHEN SelecterId = 'fruit0' THEN 'true' ELSE 'false' END 
WHERE selecterId = 'fruit0' OR Selected = 'true'

for "fruit3" UniqueText to "lemon", the another row with same value become to empty "", in this case the "fruit2"

UPDATE fruits 
SET UniqueText = CASE WHEN SelecterId = 'fruit3' THEN 'lemon' ELSE '' END 
WHERE SelecterId = 'fruit3' OR UniqeText = 'lemon'

As a general pattern:

UPDATE table
SET column_to_change = CASE WHEN identifying_column = 'id_value_you_know' ELSE 'resting_value' END
WHERE identifying_column = 'id_value_you_know' OR column_to_change = 'resting_value'

You should always know the column you change, the column you identify some record by, the value you identify by and the resting value of the column you change