Search code examples
c#sqlasp.netsql-serversql-server-ce

Update query with one unique value other one with varying values

Want a query possibly a single query for the below conditions

Sample Table

  1. To be updated value of column 3 and 4 is same for all 4 R2IGTNo column
  2. Sometimes the blend will be up to 3 only, 4th row may not come
  3. In C# application user will update for the first row (blend 1), other values have to be updated automatically since the values are the same.

I need a query, expert please help with this... Thanks

Solution

  • User picks row 1, this means your C# can know "Lot21009BB1C3" (putting text instead of image would have let me copy it, btw) but you write like the c# app doesn't know any other row..

    Which means you can run a query:

    UPDATE t SET HtrOnBy = @HtrOnBy, ChtrOnRemark = @ChtrOnRemark
WHERE R2IGTNo LIKE @R2IGTNo

    And your c# shall put the following values:

    @HtrOnBy = "Whatever it knows the first row HtrOnBy is"
@ChtrOnRemark = "Whatever it knows the first row ChtrOnRemark is"
@R2IGTNo = firstRowR2IGTNo.Remove(9) + "%"

    We take the first 9 characters of R2IGTNo and add a percent. SQL will update all the rows that start with those, doesn't matter if it's 3 or 4 etc

    If the number of chars varies, then you will have to get more smart about how you do your substring, such as looking for the first character after the numbers (eg regex)