SQL How do i update like this?

Question

I'm trying to update all SQL rows in the [Temp_LTGData] table setting the [CORP_REG_NO] value to the value in another row in the same table where the [CUSTOMER_NUMBER] matches.

Ultimately I need to do this with quite a few columns, does anyone know if this can be done?

I can't seem to use the LTGSource alias like in a select query :(

 Update [MandS].[dbo].[Temp_LTGData] LTGSource
 Set [CORP_REG_NO] = (SELECT [CORP_REG_NO] 
 FROM [MandS].[dbo].[Temp_LTGData] 
 WHERE ([CORP_REG_NO] IS NULL and [CUSTOMER_NUMBER] = LTGSource.[CUSTOMER_NUMBER] ))
    where [CORP_REG_NO] IS NULL

Thanks for hte feedback guys that's some really awesome stuff, I even learnt some different approaches to this problem (voted for you all).

Answer 1

Try the following to get exactly what you were doing

UDPATE [MandS].[dbo].[Temp_LTGData] LTGSource 
SET [CORP_REG_NO] = (SELECT [CORP_REG_NO] 
                     FROM [MandS].[dbo].[Temp_LTGData] 
                     WHERE ([CORP_REG_NO] IS NULL 
                          AND [CUSTOMER_NUMBER] = ToUpdate.[CUSTOMER_NUMBER] )) 
FROM {MandS].[dbo].[Temp_LTGData] ToUpdate
where [CORP_REG_NO] IS NULL

However, you have a mistake in your query I believe the subquery should be IS NOT NULL.

Answer 2

Try this:

UPDATE Temp_LTGData LTGSource
  SET Col1 = L2.Col1, Col2 = L2.Col2, Col3 = L2.Col3
FROM LTGSource L1
JOIN LTGSource L2 ON L2.CORP_REG_NO IS NOT NULL AND L1.CUSTOMER_NUMBER = L2.CUSTOMER_NUMBER
WHERE L1.CORP_REG_NO IS NULL 

That should do it for you. You're joining the updateable table to itself so you can have access to both the old row and the new row for the update. This way, you can update multiple columns at once.

Answer 3

Something like this which will allow you to deal with many columns with a source and target row

If you need to link different rows for different columns, then it's more complex

If I understand correctly, filtering to CORP_REG_NO IS NULL would only work for CORP_REG_NO of course so you don't want to filter except to restrict target and source rows independently of specific column filters as per you're question.

Update
   target
Set
   [CORP_REG_NO] = CASE WHEN target.[CORP_REG_NO] IS NULL THEN source.[CORP_REG_NO] ELSE target.[CORP_REG_NO] END,
    ...and again...
FROM
    [MandS].[dbo].[Temp_LTGData] target
    JOIN
    [MandS].[dbo].[Temp_LTGData] source ON target.[CUSTOMER_NUMBER] = source.[CUSTOMER_NUMBER]
WHERE
    a filter to restrict rows perhaps