Just wondering if someone could help me with a very simple SQL query.

I need to transfer data from table1 to table2 where table1.jobID = table2.jobID

Should I use join ?

I would just like to fill in a NULL column with data from table1, using jobID to match.

Thanks in Advance

AC

• sql-server
• where

With the limited knowledge it sounds like all you need is a simple UPDATE:

UPDATE PreviousJobsTEST
SET dValuationDate = Jobs.dValuationDate
FROM PreviousJobsTEST
    JOIN Jobs
        ON PreviousJobsTEST.iJobID= Jobs.iJobID

If you only want it updated if it is null, then you can do this:

UPDATE PreviousJobsTEST
SET dValuationDate = CASE WHEN PreviousJobsTEST.dValuationDate IS NULL THEN Jobs.dValuationDate ELSE PreviousJobsTEST.dValuationDate END
FROM PreviousJobsTEST
    JOIN Jobs
        ON PreviousJobsTEST.iJobID= Jobs.iJobID

OR, based on your comment, you could do what you have listed, you just need to turn it into a SELECT behind the insert:

INSERT INTO [DocPrep].[dbo].[PreviousJobsTEST] (dValuationDate) 
SELECT [DocPrep].[dbo].[Jobs].[dValuationDate]
FROM [DocPrep].[dbo].[PreviousJobsTEST]
    JOIN [DocPrep].[dbo].[Jobs] 
        ON [DocPrep].[dbo].[Jobs].[iJobID] = [DocPrep].[dbo].[PreviousJobsTEST].[iJobID]

You need to update when t1.column is null . and when t1.column is not null you should set it to itself.

SELECT *
INTO   #table1
FROM   (SELECT '1'  AS jobid,
               NULL AS column1
        UNION ALL
        SELECT '2',
               NULL
        UNION ALL
        SELECT '3',
               NULL
        UNION ALL
        SELECT '4',
               'A'
        UNION ALL
        SELECT '5',
               NULL) a

SELECT *
INTO   #table2
FROM   (SELECT '1' AS jobid,
               '1' AS column1
        UNION ALL
        SELECT '2',
               '2'
        UNION ALL
        SELECT '3',
               '3'
        UNION ALL
        SELECT '4',
               '4'
        UNION ALL
        SELECT '5',
               '5')b

UPDATE t1
SET    t1.column1 = ( CASE
                        WHEN t1.column1 IS NULL THEN t2.column1
                        ELSE t1.column1
                      END )
FROM   #table1 t1
       LEFT JOIN #table2 t2
         ON t1.jobid = t2.jobid

SELECT *
FROM   #table1