CODEWITHSUNDEEP
sqlsql-servert-sql
frans
frans

Reputation: 181

Update columns from one table with appropriate column values from another table

This question ties to my previous question:

Updating only ID's with the latest date SQL (2 of 6)

I have the following 2 tables:

Table1 and Table2

I already have this query that updates the ID's in Table1 where only the latest date is found. However, is it possible to match up the appropriate ID's on Table1 with the appropriate ID's on Table2 based on Date.

Please see my query below:

Update Dairy 
SET DY_H_ID = (
              SELECT MAX(ID) 
              FROM History 
              WHERE H_DateTime <= DY_Date 
              AND H_IDX = DY_IDX 
              AND H_HA_ID = 7 
              AND H_HSA_ID = 19 
              AND H_Description LIKE 'Diary item added for :%'
              ) 
WHERE DY_H_ID IS NULL 
AND DY_IDX IS NOT NULL
AND DY_Date = (SELECT MAX(DY_Date) FROM Dairy)

I'd like to do something like this to match the rest up, however this doesn't work:

AND SUBSTRING(CAST(DY_Date AS varchar(11)), 1, 10) = (SELECT SUBSTRING(CAST(H_DateTime AS varchar(11)), 1, 10) FROM History)

E.g. ID 10029 in Table 1 should get ID 3205 from History. ID 10030 should get the ID of 3206, ID 10031 should get ID 3207, ID 10032 should get 3208 etc etc.

Note the values in both Tables will change.

Upvotes: 0

Views: 1496

Answers (3)

frans
frans

Reputation: 181

I found it!! Thanks to @Secret Squirrel and @Gidil for pointing me in the right direction.

;WITH cte AS (
    SELECT H_IDX, MAX(ID) MaxID, SUBSTRING(H_Description, 24, 10) AS [Date]
    FROM History
    WHERE H_HA_ID = 7 
          AND H_HSA_ID = 19 
          AND H_Description LIKE 'Diary item added for :%'
    GROUP BY H_IDX, H_DateTime, H_Description
)
Update Dairy 
SET DY_H_ID = MaxID
FROM Dairy 
INNER JOIN CTE ON cte.H_IDX = DY_IDX
WHERE DY_H_ID IS NULL 
AND DY_IDX IS NOT NULL
AND DATEPART(YYYY, DY_Date) = SUBSTRING(cte.[Date], 1, 4)
AND DATEPART(MM, DY_Date) = SUBSTRING(cte.[Date], 6, 2)
AND DATEPART(DD, DY_Date) = SUBSTRING(cte.[Date], 9, 2)

Please feel free to have a look and comment on this.

Upvotes: 1

Gidil
Gidil

Reputation: 4137

I haven't been able to figure out exactly what you want :-( but this query should point you in the right direction:

UPDATE D 
SET    D.DY_H_ID = H.ID 
FROM   DAIRY D 
       INNER JOIN HISTORY H 
               ON Cast(D.DY_DATE AS VARCHAR(11)) = Cast( 
                  H.H_DATETIME AS VARCHAR(11)) 
WHERE  DY_H_ID IS NULL 
       AND DY_IDX IS NOT NULL

This UPDATE query uses a JOIN in order to compare the dates between the two tables. I think this is what you were asking for, right?

Upvotes: 1

Squirrel5853
Squirrel5853

Reputation: 2406

Could you not use DATEPART to solve this http://msdn.microsoft.com/en-us/library/ms174420.aspx 

UPDATE Dairy 
SET DY_H_ID = (
          SELECT MAX(ID) 
          FROM History 
          WHERE H_DateTime <= DY_Date 
          AND H_IDX = DY_IDX 
          AND H_HA_ID = 7 
          AND H_HSA_ID = 19 
          AND H_Description LIKE 'Diary item added for :%'
          ) 
WHERE DY_H_ID IS NULL 
AND DY_IDX IS NOT NULL
AND DY_Date = (SELECT MAX(DY_Date) FROM Dairy)

AND DATEPART(yyyy, DY_Date) = DATEPART(yyyy, H_DateTime)
AND DATEPART(mm, DY_Date) = DATEPART(mm, H_DateTime)
AND DATEPART(dd, DY_Date) = DATEPART(dd, H_DateTime)

Upvotes: 1

Related Questions