1

i want to update column p1 but p1 same product.

update column p1 where example g6-1se in C1 exist in column name and update value null in column p1 in rows g6-1se by value p1 in row g6-1sr.

in rows 1 , read g6-1 and search in column name and update column p1 if exist. after read g6-1sr and search Until you reach the finish line in row 1 column c1 , Then pointer,point to the line before update is not null column p1.

tbl before update:

    +-------+-------------+-----------------------------------------------+-------+
    | id    | Name        | C1                                            | P1    |
    +-------+-------------+-----------------------------------------------+-------+
    | 29214 | g6-1sr      | g6-1, g6-1sr, g6-1se,g6-1se12,g6-1se1         | 28000 |
    | 29215 | g6-1se      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
    | 29226 | g6-1sf      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
    | 29237 | g6-1se1     | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
    | 29248 | g6-1se12    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
    | 29259 | Nkg6-1se    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
    | 29269 | N56-1341se  | N56-11, N56-1341se, N56-1100, N56-1348se,     | 32000 |
    | 29270 | N56-1348se  | N56-11, N56-21, N56-1100, N56-2980,           | null  |
    | 29271 | F566 1341se | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 38000 |
    +-------+-------------+-----------------------------------------------+-------+

tbl after update:

    +-------+-------------+-----------------------------------------------+-------+
    | id    | Name        | C1                                            | P1    |
    +-------+-------------+-----------------------------------------------+-------+
    | 29214 | g6-1sr      | g6-1, g6-1sr, g6-1se,g6-1se12,g6-1se1         | 28000 |
    | 29215 | g6-1se      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
    | 29226 | g6-1sf      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
    | 29237 | g6-1se1     | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
    | 29248 | g6-1se12    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
    | 29259 | Nkg6-1se    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
    | 29269 | N56-1341se  | N56-11, N56-1341se, N56-1100, N56-1348se,     | 32000 |
    | 29270 | Asus N56-1348se  | N56-11, N56-21, N56-1100, N56-2980, N56-2| 32000 |
    | 29271 | F566 1341se | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 38000 |
    +-------+-------------+-----------------------------------------------+-------+

try with cursor but not successfull

3

Check it: dbfiddle here

Basically, get all rows where P1 is null, and join its with all rows that have a P1 value and where name like c1.

Using LIKE

UPDATE     t1 
SET        P1 = t2.P1
FROM       tbl1 t1
INNER JOIN tbl1 t2 
ON         t2.P1 IS NOT NULL 
AND        t2.c1 LIKE '%' + t1.Name + '%'
WHERE      t1.P1 IS NULL;

|   id|Name       |C1                                          |   P1|
|----:|:----------|:-------------------------------------------|----:|
|29214|g6-1sr     |g6-1, g6-1sr, g6-1se,g6-1se12,g6-1se1       |28000|
|29215|g6-1se     |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|28000|
|29226|g6-1sf     |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|     |
|29237|g6-1se1    |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|28000|
|29248|g6-1se12   |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|28000|
|29259|Nkg6-1se   |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|     |
|29269|N56-1341se |N56-11, N56-1341se, N56-1100, N56-1348se,   |32000|
|29270|N56-1348se |N56-11, N56-21, N56-1100, N56-2980,         |32000|
|29271|F566 1341se|g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|38000|

Using dbo.SplitCompare() function

UPDATE     t1 
SET        P1 = t2.P1
FROM       tbl1 t1
INNER JOIN tbl1 t2 
ON         t2.P1 IS NOT NULL 
AND        dbo.SplitCompare(t2.C1, t1.Name) = 1
WHERE      t1.P1 IS NULL;

Update

Check it: dbfiddle here

First, I've added a new function: [dbo].[SplitCompare]

-------------------------------------------------------------------------------------
-- Compares each item of a coma-delimited string (without trainling spaces), 
-- against @ToSearch.
--
-- Returns: (int)
--
--    1 - @ToSearch matches some item
--    0 - No matches found.
-------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[SplitCompare] (@List nvarchar(MAX), @ToSearch nvarchar(1024))
RETURNS int
AS
BEGIN
    DECLARE @Item nvarchar(1024) = null;
    DECLARE @Result int = 0;

    WHILE LEN(@List) > 0
    BEGIN
        IF PATINDEX('%,%', @List) > 0
        BEGIN
            SET @Item = SUBSTRING(@List, 0, PATINDEX('%,%', @List));
            SET @List = SUBSTRING(@List, LEN(@Item + ';') + 1, LEN(@List));
        END
        ELSE
        BEGIN
            SET @Item = @List;
            SET @List = NULL;
         END

    IF RTRIM(LTRIM(@Item)) = @ToSearch -- does it match?
    BEGIN
        SET @Result = 1;
        SET @List = '';
    END
    END

    RETURN @Result;

END

Second, according to OP requirement, I've implemented this solution using a CURSOR.

-------------------------------------------------------------------------------------
-- Use @LimitRows just to limit the number of rows to be updated each time 
-- the script is executed.
-------------------------------------------------------------------------------------
DECLARE @LimitRows int = 100;
DECLARE @Id int, @Name nvarchar(2048), @P1 int;

-- only selects rows where P1 IS NULL
DECLARE CurPrices CURSOR
    FOR SELECT TOP (@LimitRows) Id, Name FROM #tbl1 WHERE P1 IS NULL;

OPEN CurPrices 
FETCH NEXT FROM CurPrices INTO @Id, @Name

WHILE @@FETCH_STATUS = 0  
BEGIN

    SET @P1 = NULL;

    -- note: top (1)
    --
    SELECT   TOP (1) @P1 = t.P1
    FROM     tbl1 t
    WHERE    t.P1 IS NOT NULL
    AND      dbo.SplitCompare(t.C1, @Name) = 1
    ORDER BY Id;

    IF COALESCE(@P1,0) > 0
    BEGIN
        UPDATE #tbl1
        SET    P1 = @P1
        WHERE  Id = @Id;

        IF @@ERROR <> 0
        BEGIN
           CLOSE CurPrices;
           DEALLOCATE CurPrices;
           RAISERROR('ERROR', 20, -1);
        END
    END

    FETCH NEXT FROM CurPrices INTO @Id, @Name    
END

CLOSE CurPrices;
DEALLOCATE CurPrices;

The result is the same as the first command, but this one allow to limit the number of affected rows every time it is executed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.