CODEWITHSUNDEEP
sqlsql-servert-sql
Twil
Twil

Reputation: 107

Found and replace specific value T-SQL

I have such table

+------------+------------+----------+
| Name       | Class      | Launched |
+------------+------------+----------+
| California | California | 1921     |
+------------+------------+----------+
| Haruna     | California | 1916     |
+------------+------------+----------+
| Test       | California | NULL     |
+------------+------------+----------+

I need to replace NULL value with value from the cell where:

I do this using function, but I think it's not the best solution. 

CREATE FUNCTION fn_mainprod (@classname varchar(50))
RETURNS int
    AS
    BEGIN
        RETURN
        (
        SELECT launched from Ships
        WHERE name = class AND class = @classname
        );
    END
GO

SELECT name, class,
CASE
    WHEN launched IS NULL THEN fn_mainprod(class)
    ELSE launched
END as Prod
FROM Ships

Output:

+------------+------------+----------+
| Name       | Class      | Prod     |
+------------+------------+----------+
| California | California | 1921     |
+------------+------------+----------+
| Haruna     | California | 1916     |
+------------+------------+----------+
| Test       | California | 1921     |
+------------+------------+----------+

Could you please recommend, how to do this without using function.

Upvotes: 1

Views: 39

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use outer apply :

select s.name, s.class, coalesce(s.Launched, s2.Launched) as Prod
from Ships s outer apply 
     (select top (1) s1.Launched 
      from Ships s1
      where s1.class = s.class and s1.name = s.class and
            s.Launched is null
      order by ?
     ) s2;

Upvotes: 1

Related Questions