SQL UPDATE (copy from row to row)-CodePudding

I want to try to copy a value from one record in a Table to another record in the same table an same column.

    UPDATE
    [PDF]
SET
    [PDF].[description] = [MISC].[description]
FROM (
    SELECT
          [PDF].[TPYE]
         ,[ID]
         ,[PDF].[description]
    FROM [DB1] AS [PDF]
    
    INNER JOIN (
        SELECT
             [TYPE]
             [description]
             [ID]
        FROM [DB1]
        WHERE [TYPE] = 'MISC'
            ) AS [MISC] ON [PDF].[ID] = [MISC].[ID]
WHERE
    [PDF].[TPYE] = 'PDF'

For more clarification ive added a screenshot.enter image description here

The Table contains a few thousands rows, and i want to copy the description from one row to another - dependent on NUMBER and TYPE.

CodePudding user response：

Have a look at this as an example. You were pretty close.

DECLARE @DB1 AS TABLE ( ID          INT           NOT NULL
,                       TYPE        NVARCHAR(4)   NOT NULL
,                       Description NVARCHAR(255) NULL )

INSERT INTO @DB1 ( ID, TYPE, Description )
VALUES ( 1, N'PDF', N'' )
,      ( 1, N'MISC', N'Description 1' )
,      ( 2, N'PDF', N'' )
,      ( 2, N'MISC', N'Description 2' )

SELECT  *
  FROM  @DB1
 WHERE  TYPE = 'PDF'

UPDATE  PDF
   SET  PDF.Description = MISC.Description
  FROM  @DB1 PDF
  JOIN  @DB1 MISC ON MISC.ID = PDF.ID
                 AND MISC.TYPE = 'MISC'
 WHERE  PDF.TYPE = 'PDF'

SELECT  *
  FROM  @DB1
 WHERE  TYPE = 'PDF'

As a note, in the future it helps if you can include some sample data for other readers to work with. The way I created a table structure and populated it with some test data is a basic example of how this can be done.

If you find that an answer is useful and correct then don't forget to mark it as such and upvote. That will help other users know what has worked. That applies across the site in general not just your own questions.