Split alphanumeric string into numeric and alpha parts in SQL Server

Lets say we have an alphanumeric string stored in a variable in SQL Server. The fist part of the string is numeric and the second part consists of letters and/or symbols specifying a unit or a percentage:

DECLARE @str VARCHAR(MAX) = '4000 ug/ML'

We now want to split the numeric / unit parts from the variable and return them as separate fields.

If the numeric part is always at the beginning, then we can use this expression:

PATINDEX('%[0-9][^0-9]%', @str)

to get the index of the last digit. To see how this works let us have a look at the manual for PATINDEX:

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

The pattern specified catches the first occurrence of a number, [0-9], followed by a character that is not a number: [^0-9]. In other words, it returns the index of the last number.

Thus, this:

SELECT LEFT(@str, PATINDEX('%[0-9][^0-9]%', @str )) AS Number,
       LTRIM(RIGHT(@str, LEN(@str) - PATINDEX('%[0-9][^0-9]%', @str ))) As Unit

returns the following:

Number	Unit
4000	ug/ML

Click here for a live demo.

If numeric data include double values as well, like:

DECLARE @str VARCHAR(MAX) = '4000.35 ug/ML'

In this case we can use:

SELECT LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))

Using PATINDEX on the REVERSE order of the string value gets us the index of the first digit from the end of the string that follows a character that is not a number. In our example the SELECT returns 7 indicating the index of number 5 from the end of the string.

Thus, this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str)))

gives us the numeric part, and this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))) AS Numeric,
       CASE 
          WHEN CHARINDEX ('%', @str) <> 0 THEN LTRIM(RIGHT(@str, LEN(@str) - CHARINDEX ('%', @str)))
          ELSE LTRIM(RIGHT(@str, PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))))
       END AS Unit

gives us both numeric and unit part.

Click here for a live demo.

How to fix 'Parameters were not supplied for the function' error in SQL Server

SQL Server provides a feature called Table Valued Functions. These are user-defined functions that return a table data type and can be powerful alternatives to views.

Lets try to define a simple function of this kind using the following code:

CREATE FUNCTION X
(
    -- Add the parameters for the function here
    @a nvarchar(30), 
    @b nvarchar(30)
)
RETURNS 
@X TABLE 
(
    -- Add the column definitions for the TABLE variable here
    data nvarchar(250)
)
AS
BEGIN

    INSERT INTO X (data) 
    VALUES (@a + @b)    

    RETURN 
END
GO

The function just returns a table that contains the concatenation of two strings. We now try to use the function:

SELECT *
FROM X('Hello', 'World')

We expect to get a table that contains a single record but instead we get the following error message:

Parameters were not supplied for the function 'X'.

Click here for a live demo of the error.

The problem with our code is that it declares a table variable to be returned by the function that has the same name as the function. This is syntactically correct but then, instead of doing an INSERT into the table variable, it uses the function name, which happens to be the same.

What this type of, difficult to decipher, error teaches us is that it is much cleaner to use a different name for the table variable to be returned.

CREATE FUNCTION X
(
    -- Add the parameters for the function here
    @a nvarchar(30), 
    @b nvarchar(30)
)
RETURNS 
@output TABLE 
(
    -- Add the column definitions for the TABLE variable here
    data nvarchar(250)
)
AS
BEGIN

    INSERT INTO @output (data) 
    VALUES (@a + @b)    

    RETURN 
END
GO

Click here for a live demo.

Pivot a table in SQL Server that contains yearly data

Consider the following table recording expenses:

CREATE TABLE dbo.Expenses
(
    ExpenseId INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
    ExpenseType VARCHAR(20) NOT NULL,
    Amount DECIMAL(6,2) NOT NULL,
    ExpenseDate DATE NOT NULL
)   

We populate the table with the following data:

ExpenseId	ExpenseType	Amount	ExpenseDate
1	Rent	340.50	2017-01-01
2	Food	23.50	2017-01-03
3	Food	27.50	2017-04-08
4	Gasoline	50.30	2017-01-05
5	Food	27.50	2016-01-08
6	Rent	340.50	2016-01-01
7	Food	85.00	2015-07-17
8	Gasoline	48.40	2015-12-15
9	Furniture	540.00	2015-09-22
10	Rent	335.00	2015-01-01

Our aim is to get a pivoted view of this table:

ExpenseType	2015	2016	2017
Food	540.00	0.00	0.00
Furniture	85.00	27.50	51.00
Gasoline	48.40	0.00	50.30
Rent	335.00	340.50	340.50

If the list of years is static, in other words if we know before-hand the list of years to be taken into account, then we can make use of the PIVOT relational operator:

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Here, we want to turn the unique year values of column ExpenseDate:

• 2015
• 2016
• 2017

into multiple columns and perform aggregation on column Amount. This operation will be performed for each value of column ExpenseType.

To get the year values of ExpenseDate column we use SQL Server's YEAR built-in function. So, the table-valued expression that PIVOT will operate on looks like this:

SELECT ExpenseType, YEAR(ExpenseDate) AS ExpenseYear, Amount
FROM Expenses

PIVOT is performed for column ExpenseYear, whereas Amount column is the one to be aggregated using SUM aggregate function:

PIVOT
( 
   SUM(Amount)
   FOR ExpenseYear IN ([2015], [2016], [2017])
) AS PivotTable

Hence, the query we want looks like this:

SELECT ExpenseType, 
       COALESCE([2015], 0) AS [2015], 
       COALESCE([2016], 0) AS [2016], 
       COALESCE([2017], 0) AS [2017]
FROM (SELECT ExpenseType, YEAR(ExpenseDate) AS ExpenseYear, Amount
      FROM Expenses) AS SourceTable
PIVOT
( 
   SUM(Amount)
   FOR ExpenseYear IN ([2015], [2016], [2017])
) AS PivotTable

Note the usage of COALESCE around each year column: in case there are no values for a specific Expense Type - ExpenseDate pair, then 0 is produced in the output.

Click here for a live demo.

How to record the current date/time value in SQL Server?

Say we have the following table in SQL Server recording incoming payments:

CREATE TABLE Payment
(
   PaymentId INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
   PaymentType VARCHAR(15) NOT NULL,
   Amount DECIMAL(6,2),
   PaymentDate DATETIME NOT NULL
)

We would like to record in PaymentDate the date and time value each payment record is inserted into the table.

One way to do so is to use the current date/time timestamp when a new record is inserted into the table. So, inserting a new record could look like this:

INSERT INTO Payment (PaymentType, Amount, PaymentDate)
VALUES
('Payment Owed', 20.12, GETDATE()),
('Gift', 15.47, GETDATE())

This INSERT statement uses GETDATE built-in function, which

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

The table after this INSERT takes place looks like:

PaymentId	PaymentType	Amount	PaymentDate
1	Payment Owed	20.12	2017-05-28 09:24:12.237
2	Gift	15.47	2017-05-28 09:24:12.237

A more convenient way around this problem would be to add a default constraint to PaymentDate column:

ALTER TABLE Payments
ADD CONSTRAINT df_CurrentDateTime
DEFAULT CURRENT_TIMESTAMP FOR [Payment_Date]

The constaint is just a default value for column PaymentDate. This value is the value returned by built-in function CURRENT_TIMESTAMP:

This function is the ANSI SQL equivalent to GETDATE.

So, the function does the same thing with GETDATE. If we add the constraint, the INSERT statement is simplified to:

INSERT INTO Payment (PaymentType, Amount)
VALUES
('Payment Owed', 20.12),
('Gift', 15.47)

Comparing two decimal values in SQL Server

Lets say we declare and initialize two decimal variables in SQL Server like this:

DECLARE @num1 DECIMAL = 1.98
DECLARE @num2 DECIMAL = 2.2

then we use the following piece of code to compare the two values:

IF (@num1 != @num2)
   SELECT 0
ELSE IF (@num1 = @num2)
   SELECT 1

Contrary to what one might expect the above code returns a 1, implying the two values are equal! Click here for a live demo.

This is due to the fact that we have not included the precision and scale values in our declaration of @num1, @num2 variables. Hence, default values are being used.

According to the documentation:

Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

So the scale of both variables defaults to 0 and thus both variables are being set to 2.

We can easily get around the problem by using declaration statements like:

DECLARE @num1 DECIMAL(10,2) = 1.98
DECLARE @num2 DECIMAL(10,2) = 2.2

Identify record sequences matching a predefined pattern in SQL Server

We have a set of integer values that define a pattern: {5, 2, 6, 8}.

Our aim is to identify record sequences that match this pattern and return them with a SELECT query.

With the following table as input:

Id	Val
1	5
2	2
3	6
4	8
5	5
6	2
7	5
8	2
9	6
10	8

we want to produce the following output:

Id	Val
1	5
2	2
3	6
4	8
7	5
8	2
9	6
10	8

Field Id is an auto-increment primary key that uniquely identifies table records and also determines row order.

The first thing we need to do is add sequence numbers to the set that defines the pattern. We can use the following query wrapped in a Common Table Expression (CTE) to do so:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 2), (3, 6), (4, 8)) x(k,v)
)

Output:

v	rn
5	1
2	2
6	3
8	4

Using the above CTE we can identify islands, i.e. slices of sequential rows containing the whole of the sequence:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 2), (3, 6), (4, 8)) x(k,v)
), Grp AS (
SELECT Id, Val, 
       ROW_NUMBER() OVER (ORDER BY Id) - rn AS grp            
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Val = s.v
)
SELECT *
FROM Grp;

Output:

Id	Val	grp
1	5	0
2	2	0
3	6	0
4	8	0
5	5	4
6	2	4
7	5	6
8	2	6
9	6	6
10	8	6

All we need to do now is to just filter out partial groups:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 2), (3, 6), (4, 8)) x(k,v)
), Grp AS (
SELECT Id, Val, 
       ROW_NUMBER() OVER (ORDER BY Id) - rn AS grp            
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Val = s.v
)
SELECT g1.Id, g1.Val
FROM Grp AS g1
INNER JOIN (
   SELECT grp
   FROM Grp
   GROUP BY grp
   HAVING COUNT(*) = 4 ) AS g2
ON g1.grp = g2.grp

Click here for a live demo.

Create a Cartesian product of all records of a table using a set of predefined values

Suppose we have a table like below in SQL Server:

CREATE TABLE dbo.mytable
(
    Name VARCHAR(20),
    Val INT
)

and we populate it using:

INSERT INTO dbo.mytable
VALUES
('A', 10),
('B', 5)
('C', 12)

We now want to create a Cartesian product of those records using a small set of date values

Date
2017-01-01
2017-02-01
2017-03-01

Using the above set of date values the required output is:

Name	Val	Date
A	10	2017-01-01
A	10	2017-02-01
A	10	2017-03-01
B	5	2017-01-01
B	5	2017-02-01
B	5	2017-03-01
C	12	2017-01-01
C	12	2017-02-01
C	12	2017-03-01

The first step in solving our problem is to create an in-line table containing the date values making use of a Table Value Constructor.

To create the Cartesian product we can use a CROSS JOIN that does exactly this:

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

So, our query looks like this:

SELECT  Name, Val, x.myDate
FROM mytable
CROSS JOIN (VALUES ('2017-01-01'), 
                   ('2017-02-01'), 
                   ('2017-03-01')) x(myDate)
ORDER BY Name, myDate

Click here for a live demo.

How to initialize several SQL Server variables with values stored in a temp table.

Suppose we have the following temporary table in SQL Server:

CREATE TABLE #myvalues (val int)

populated with values:

INSERT INTO #myvalues 
VALUES
(10), (15), (11), (18), (22)

We want to store these values in different variables like @v1, @v2, @v3, @v4, @v5. Is it possible to do so in a single SQL query? The answer is yes.

Consider the following query using ROW_NUMBER:

SELECT val, 
       ROW_NUMBER() OVER (ORDER BY val) AS rn
FROM #myvalues

it yields this output:

val	rn
10	1
11	2
15	3
18	4
22	5

So, what the query essentially does is that it assigns a distinct number to each record of the temporary table.

We can now use a technique called conditional aggregation on the output produced by the previous query, in order to set the values of all 5 variables with a single query:

SELECT @v1 = MAX(CASE WHEN rn = 1 THEN val END),
       @v2 = MAX(CASE WHEN rn = 2 THEN val END),
       @v3 = MAX(CASE WHEN rn = 3 THEN val END),
       @v4 = MAX(CASE WHEN rn = 4 THEN val END),
       @v5 = MAX(CASE WHEN rn = 5 THEN val END)
FROM (
   SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn
   FROM #myvalues ) t

This technique uses a CASE expression inside an aggregate function, like MAX in our case. Each usage of the conditional aggregate produces a separate field that corresponds to a distinct record of the temporary table.

So, for example,

@v1 = MAX(CASE WHEN rn = 1 THEN val END)

assigns @v1 the value of the first record of the table.

Click here for a live demo.

Compare integer with string in MySQL

Lets say we have a table with the following structure:

CREATE TABLE people 
(
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age TINYINT,
    PRIMARY KEY (id)
)

Let us also insert some sample data to have something to play with:

INSERT INTO people (first_name, last_name, age) 
VALUES
('Giorgos', 'Betsos', 49),
('Tim', 'Smith', 35),
('Bob', 'No Age', 0);

Note that there is no need to specify id value in the INSERT as this is an AUTO INCREMENT key. Also, no age is available for 'Bob', so we choose to represent this fact with a 0 value.

If we run this query:

SELECT *
FROM people
WHERE age = 'old';

we get:

id	first_name	last_name	age
3	Bob	No age	0

Click here for a live demo.

So, one record is returned despite there is no record with age='old' in the table. How can this be possible?

We start to realize what is going on here if we execute simple query:

SELECT 'old' + 0

This seems not to make sense. However the query executes and returns a value of 0. This is because of implicit type conversion. String value 'old' is converted to 0 and then added to 0 and thus 0 is returned.

The same thing happens with our query. 'old' is converted to 0 when compared with a field of type int.

Select a record and return default value if record doesn't exist

Consider the following set of SQL data in SQL Server:

Model	Discount
Honda	15
Toyota	17
Default	10

We also have a query parameter stored in a variable called @Model_name. We want to query the table using this variable. If a record is found then we want to return it, otherwise we want to return the record with Model = 'Default'.

To get this result we can use this query:

SELECT TOP 1 Discount
FROM mytable
WHERE Model = @Model_name OR Model = 'Default'
ORDER BY CASE 
            WHEN Model = 'Default' THEN 1
            ELSE 0 
         END

The trick here is to use a CASE expression in the ORDER BY clause in order to prioritize records that match @Model_name value over 'Default' record.

The query returns always one record. In case Model field contains duplicates and we want all of them returned, then we can use the following query:

;WITH CTE AS (
    SELECT Discount,
           RANK() OVER (ORDER BY CASE 
                                    WHEN Model = 'Default' THEN 1
                                    ELSE 0 
                                  END) AS rnk
    FROM mytable
    WHERE Model = @Model_name OR Model = 'Default'
)
SELECT Discount
FROM CTE
WHERE rnk = 1

This query uses RANK window function in order to prioritize. The trick here is to use a CASE expression in the ORDER BY of OVER clause. This way RANK assigns a value of 1 to all records that match @Model_name value.

Get row numbers of records inside a group, also get the population of each group

Consider the following set of values:

PK	ID	State
1	1	TX
2	1	AZ
3	1	CA
4	1	NV
5	2	FL
6	2	AL
7	2	GA
8	3	NY
9	3	MA

Field PK is an auto-increment primary key, that uniquely identifies each record and also determines row order. Field ID groups together table records.

What we want to do here is to enumerate records inside each group, as well as to ouput the population of each group. This is the result we would like to see:

PK	ID	State	Rn	Cnt
1	1	TX	1	4
2	1	AZ	2	4
3	1	CA	3	4
4	1	NV	4	4
5	2	FL	1	3
6	2	AL	2	3
7	2	GA	3	3
8	3	NY	1	2
9	3	MA	2	2

In SQL Server this kind of result can be easily achieved using Window Functions. As their name implies, window functions operate on windows of data. We can determine how each window looks like using OVER clause. Using the following clause:

OVER (PARTITION BY ID ORDER BY PK)

produces these windows of data:

1. (1, 1, 'TX')-> (2, 1, 'AZ')-> (3, 1, 'CA')-> (4, 1, 'NV')
2. (5, 2, 'FL')-> (6, 2, 'AL')-> (7, 2, 'GA')
3. (8, 3, 'NY')-> (9, 3, 'MA')

We can easily get at the desired output using ROW_NUMBER to enumerate the records inside each partition and windowed version of COUNT aggregate function to get the population of each partition:

SELECT ID, State, 
       ROW_NUMBER OVER (PARTITION BY ID ORDER BY PK) AS rn,
       COUNT(*) OVER (PARTITION BY ID) AS Cnt
FROM mytable

How to fix 'Illegal mix of collations' issue

You might have come across the following error in MySQL:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

after executing a SQL SELECT statement as simple as:

SELECT *
FROM MyPersons
WHERE name NOT IN (SELECT name FROM MyUsers);

You can view an on-line demo of this case here:

We can understand the origins of this error if we check the MySQL on-line manual:

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

So, what causes the problem in our case is that despite the two tables, MyPersons, MyUsers, share the same character set, their collations are not the same. Due to this fact MySQL is unable to perform a comparison between the fields called name.

Let us have a look at how the two tables are created:

CREATE TABLE MyPersons (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci
);

CREATE TABLE MyUsers ( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci
);

To solve the problem we can override the collation of table MyUsers using the COLLATE clause:

SELECT *
FROM MyPersons 
WHERE name NOT IN (SELECT name COLLATE utf8_general_ci
                   FROM MyUsers);

Selectively check for duplicates in SQL Server

Suppose we have a table in our relational database that stores Person phone numbers, like:

Id	PersonId	Phone	IsPrimary
1	1	2610-9346551	1
2	1	2610-8346552	0
3	2	2610-7346553	1
4	3	2610-6346544	1
5	3	2610-7346555	0
6	3	2610-5346556	0

IsPrimary is a bit field that determines the primary number of each person. We want to add a constraint to this table that ensures each person has at most 1 primary number.

So, after adding the constraint, an attempt to insert the following record to the table:

Id	PersonId	Phone	IsPrimary
7	1	2610-3376551	1

will fail, whereas an attempt to insert this record:

Id	PersonId	Phone	IsPrimary
7	1	2610-3376551	0

will be carried out without any problem.

SQL Server, starting with version 2008, has a feature that makes implementing this kind of selective unique contraint very easy. It is called a Unique Filtered Index and it is implemented as simply as:

CREATE UNIQUE INDEX UQ_Person_isPrimary
    ON Person (PersonId, IsPrimary)
    WHERE IsPrimary = 1

Filter a table having a multitude of integer fields

Suppose we have an SQL table having a primary key field plus a multitude of integer columns as follows:

Id	Col1	Col2	Col3	Col4	Col5	Col6
1	4	-1	2	8	7	3
2	4	-4	0	-2	5	8
3	1	6	-9	3	2	-6
4	-5	2	12	17	10	2
5	2	1	5	0	19	13

We want to filter those records selecting the ones where at least two of any of the columns have a negative value. The query should return records with Id = 2, 3.

Doing this sort of filtering using predicates in the WHERE clause of the query would result in a very complex expression that would be error-prone and difficult to maintain.

Instead we can use a Table Value Constructor in order to create an inline table containing all of the fields. Then we can query this table to get rows having at least two fields less than 0:

SELECT *
FROM mytable
CROSS APPLY (
  SELECT COUNT(*) AS cnt
  FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5), (Col6)) AS t(v)
  WHERE t.v < 0) AS x
WHERE x.cnt >= 2  

Here we have used VALUES clause in the definition of a derived table in the FROM clause. The inline table constructed this way contains 6 rows that correspond to each field of the initial table.

Perform conditional sum in Linq

Suppose we have the following integer array in C#:

int[] array = { 5, 1, -4, 0, 5, -8, 5, -3 };

Our aim is to perform a conditional sum of the values of the list excluding a subset of values that satisfy a condition, i.e. negative or positive values.

We can do this using:

double sumOfElem = array.Sum(element => (element < 0 ? 0 : element));

This code uses an overload of Sum that utilizes a transform function (so called selector), which is applied to each element of the array. The above selector filters out negative elements. To filter out positive ones, we can simply inverse the comparison operator:

double sumOfElem = array.Sum(element => (element > 0 ? 0 : element));

Update multiple second Id's to match the first Id of a group of consecutive records

Consider the following set of SQL data:

Id	Value
10	1142
11	1142
12	1142
13	1142
14	1185
15	1185
16	1185

Column Id is an auto-increment primary key. It is used to uniquely identify records as well as to specify row order. Column Value is used to group together consecutive records. The data above contain two groups:

1. (10, 1142), (11, 1142), (12, 1142), (13, 1142) is the first group
2. (14, 1185), (15, 1185), (16, 1185) is the second group

Our aim is to update Value field to match the Id value of the first record of the same group. Hence, after the UPDATE takes place the data will look like this:

Id	Value
10	10
11	10
12	10
13	10
14	14
15	14
16	14

So, we essentially want to select the first value of each group and use this value to do the update. SQL Server (starting with version 2012) implements a window function that does exactly this. It is called FIRST_VALUE. Using it is as simple as:

SELECT Id, Value,
       FIRST_VALUE(Id) OVER (PARTITION BY Val ORDER BY Id) AS newValue
FROM mytable

The query above returns in newValue the required new value for Value column. Wrapping the above query in a Common Table Expression (CTE) gives us easily the UPDATE:

;WITH ToUpdate AS (
   SELECT Id, Val,
          FIRST_VALUE(Id) OVER (PARTITION BY Val ORDER BY Id) AS newValue
   FROM mytable
)
UPDATE ToUpdate
SET Val = newValue

What happens here is that the UPDATE is performed on the CTE, but in reality it is propagated to the actual records in the database.

Unfortunately FIRST_VALUE isn't available in SQL Server versions 2008/2005. In this case we can use the following query:

;WITH CTE_Rn AS (
   SELECT Id, Val,
          ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Id) AS rn
   FROM mytable
), ToUpdate AS (
   SELECT t1.Id, t2.Val, t2.Id AS newValue
   FROM mytable AS t1
   JOIN CTE_Rn AS t2 ON t1.Val = t2.Val AND t2.rn = 1
)
UPDATE ToUpdate
SET Val = newValue