You must Sign In to post a response.
  • Category: SQL Server

    Update in SQL server

    I have a table in below format,

    Name1 Name2 Value
    A B 10
    NULL NULL 10
    NULL NULL 10
    C D 20
    NULL NULL 20
    NULL NULL 20

    I want to update the name1 and name2 based on the value. i want the output as below
    Name1 Name2 Value
    A B 10
    A B 10
    A B 10
    C D 20
    C D 20
    C D 20
  • #756036
    Hi,

    Try something like below to achieve this.


    declare @temp table
    (
    name1 varchar(100),
    name2 varchar(100),
    id int

    )

    insert into @temp
    values('a','b',1),(null,null,1),(null,null,1),('a','b',2),(null,null,2)

    select * from @temp
    update @temp set name1= ( select distinct name1 from @temp where id=1 and name1 is not null)
    update @temp set name2= ( select distinct name2 from @temp where id=2 and name2 is not null)

    select * from @temp


    Hope this will helpful to you to resolve the issue..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #756086
    I dint understand your requirement quite well. How you will know if the user is A or B or C or D. On what basis you want to update the table?
    Miss. Jain
    Microsoft Certified Technology Specialist in .Net

  • #756121
    Hi Siva,

    Please use the below query.

    UPDATE #tmp SET Name1= CASE WHEN Value=10 THEN 'A'
    WHEN Value=20 THEN 'C' END,

    Name2= CASE WHEN Value=10 THEN 'B'
    WHEN Value=20 THEN 'D' END

    It Will help your requirment

  • #756201
    Hi,

    Try the following query: (assuming the name of your table is T)

    Update T set Name1 = 'A' and Name2= 'B' where value = 10;

    Update T set Name1 = 'C' and Name2 = 'D' where value= 20;

    Thanks,
    Praveen

  • #756204
    Hi,

    I used the below output to get my excepted output,

    DECLARE @temp TABLE
    (
    name1 VARCHAR(100),
    name2 VARCHAR(100),
    id INT
    )

    INSERT INTO @temp
    VALUES ('AA','BB',1),(NULL,NULL,1),(NULL,NULL,1),('CC','DD',2),(NULL,NULL,2)

    UPDATE @temp
    SET name1 = t2.name1,
    name2 = t2.name2
    FROM @temp t1
    CROSS APPLY (SELECT *
    FROM @temp
    WHERE id = t1.id
    AND name1 IS NOT NULL)t2

    SELECT *
    FROM @temp

  • #756209
    DECLARE @ds VARCHAR(50)
    ds=select field1 from field where field1 is NOT NULL
    ds1=select field2 from field where field2 is NOT NULL

    update table set field1=ds,field2=ds1 where field1 is null and field2 is null


  • Sign In to post your comments