CODEWITHSUNDEEP
sqlsql-serverreplacesql-update
DeadlyDagger
DeadlyDagger

Reputation: 609

SQL Server - Update statement with condition

I have this table Tree:

ID  Name   ParentID
-------------------
1   A      NULL
2   A B    1
3   A B C  2

I want to trim the parent prefix in the name column (in my example above 'A' in the second row and 'A-B' in the third), the parent might be multiple words for simplicity I just put a letter in my example.

Update 
    Tree
Set 
    Name = REPLACE(Name,
    (Select Name 
     Where ParetnID=@ParentID), '')
Where ParentId IS NOT NULL

Im not sure how the get that @ParentID

Upvotes: 1

Views: 336

Answers (6)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Update NAME (aka Parent's Name) from the parent reference. The OP is confusing since NAME field was not explained.

update t
set name = (select top 1 name from tree m where t.parentid = m.id)
from tree t

demo here http://rextester.com/live/SXLC53543

Upvotes: 0

Shawn
Shawn

Reputation: 4786

Since you're SQL2016, you can make this easier by using some of the functions available there. This should work back to SQL2012, or SQL2008 if you change the IIF().

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t ( ID int, Name varchar(10), ParentID int ) ;
INSERT INTO t 
VALUES 
    (1,'A1',NULL)
  , (2,'A1 B2',1)
  , (3,'A1 B2 C3',2)
  , (4,'D1',NULL)
  , (5,'D1 E2',4)
  , (6,'D1 E2 F3',5)
;

The Query:

SELECT s1.Name
  , REVERSE( IIF( s1.delim-1<0 , s1.revStr, LEFT(s1.revStr,(s1.delim-1)) ) ) AS finalStr
FROM (
  SELECT Name
    , REVERSE(t.Name) as revStr
    , CHARINDEX(' ',REVERSE( t.Name )) as delim
  FROM t
) s1

Results:

|     Name | finalStr |
|----------|----------|
|       A1 |       A1 |
|    A1 B2 |       B2 |
| A1 B2 C3 |       C3 |
|       D1 |       D1 |
|    D1 E2 |       E2 |
| D1 E2 F3 |       F3 |

What I Did:

1) FROM (...) s1 >> I used a subquery to keep from having to calculate the reversed string and the position of the delimiter.

1a) If the delimiter changes, just change CHARINDEX(' ',.....).

2) Since I have the reversed string, I calculate the position of the delimiter and extract that string with LEFT(s1.revStr,(s1.delim-1)).

3) Since there may be only one element in the string (parent rows), I IIF() to see if my delimiter starts out of bounds.

4) Since I've extracted the first item of the reversed string, I now have to reverse that string back to revert to the original value.

Upvotes: 0

Von Abanes
Von Abanes

Reputation: 716

try use the LAG Function, it enables to access a row that comes before the current row.

    DECLARE @ttable TABLE
        (
          id SMALLINT ,
          name VARCHAR(10) ,
          parentid SMALLINT
        )

    INSERT  INTO @ttable
    VALUES  ( 1, 'A', NULL ),
            ( 2, 'A B', 1 ),
            ( 3, 'A B C', 2 )

    --
            ;
            WITH    CTE
                      AS ( SELECT   Id ,
                                    name ,
                                    RTRIM(LTRIM(REPLACE(name, LAG(name, 1, 0) OVER ( ORDER BY id ), ''))) rr ,
                                    parentid
                           FROM     @ttable
                         )
                UPDATE  CTE
                SET     name = rr


 SELECT  * FROM @ttable

Result

    id     name       parentid
    ------ ---------- --------
    1      A          NULL
    2      B          1
    3      C          2

    (3 row(s) affected)

Upvotes: 1

abdul qayyum
abdul qayyum

Reputation: 555

Values can be simply selected by this way:

select substring(cv,CHARINDEX(pv,cv)+LEN(pv),LEN(cv)) 
from (select ct.name cv,pt.name pv from tree ct,tree pt where pt.id=ct.parentid) r

here t is your tree table. r is the subquery by joining with itself. c prefix for child and p for parent. must work

Upvotes: 0

M.Ali
M.Ali

Reputation: 69574

Sample Data

Declare @t TABLE (ID INT ,  Name VARCHAR(100),   ParentID INT)
INSERT INTO @t VALUES 
(1   ,'A'      ,NULL),
(2   ,'A B'    ,1   ),
(3   ,'A B C'  ,2   )

Query

SELECT *
FROM @t t 
    CROSS APPLY 
            (
            SELECT   Replace(
                        RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)')))
                        ,'|' , '&') NameElement
                 ,  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 NodeLevel
            FROM 
             (
                SELECT Cast ('<X>' 
                            + Replace(Replace(t.Name, ' ', '</X><X>'), '&' , '|') 
                            + '</X>' AS XML) AS Data
              ) AS t CROSS APPLY Data.nodes ('/X') AS Split(a) 
            ) c(NameElement, NodeLevel)
WHERE t.ParentID = c.NodeLevel

Result Set

+----+-------+----------+-------------+-----------+
| ID | Name  | ParentID | NameElement | NodeLevel |
+----+-------+----------+-------------+-----------+
|  2 | A B   |        1 | B           |         1 |
|  3 | A B C |        2 | C           |         2 |
+----+-------+----------+-------------+-----------+

SQL Server 2016 and Later versions

In SQL Server 2016 STRING_SPLIT() function has made this a lot simpler. In SQL Server 2016 and later versions your query would look like:

SELECT *
FROM @t t 
  CROSS APPLY (SELECT value 
                     ,  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 NodeLevel
               FROM string_split(t.name , ' ')
            ) c(NameElement, NodeLevel)
WHERE t.ParentID = c.NodeLevel

Now you can convert this select into an update statement.

Upvotes: 0

my779
my779

Reputation: 79

Try the following: 

    Update Tree
      Set Name = REPLACE(Name,B.Name, '')
    FROM Tree A
    INNER JOIN Tree B
    ON A.ParentID = B.ID
    Where ParentId IS NOT NULL

Upvotes: 0

Related Questions