SQL How do i update like this?

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 the feedback guys that's some really awesome stuff, I even learnt some different approaches to this problem (voted for you all).

Answers


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.


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

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.


Need Your Help

Adding a UIButton to view after video gets completed playing

iphone ios xcode ios5 uibutton

As soon as i open my application i have to play a video, by the time the video is being completed i have to add a UIbutton on that video, is it possible, i have searched but i could not find a link...

String variable as name of object?

java variables loops object naming

writing a program which reads a file, and saves the information into an array of objects.