CODEWITHSUNDEEP
sqlsql-serversql-update
ledragon
ledragon

Reputation: 301

SQL Update multiple rows with value from another row

Id  JobId   Code    DateAdded   

1   100     FR1F    2018-06-22 14:19:57.807

2   100     W2OO    2018-06-22 16:52:53.160 

3   101     FR1F    2018-06-22 14:19:57.237

4   101     W2OO    2018-06-22 16:52:53.160 

5   102     FR1F    2018-06-22 14:19:48.020 

6   102     W2OO    2018-06-22 16:52:53.160

Hi, I have a result of a query, a section of which is described above with a sample of data.

Query as follows:

SELECT 
        T1.Id
    ,   T2.JobId
    ,   T3.Code
    ,   T1.DateAdded
  FROM T1 
  INNER JOIN T2 ON T1.JobId = T2.JobId 
  INNER JOIN T3 ON T1.T3Id = T3.Id
  WHERE T3.Code = 'W2OO' OR T3.Code = 'FR1F'

I have a requirement to update the DateAdded Value of the rows with Code = 'FR1F' with the DateAdded value from the rows with Code = 'W200'

Hopefully a simple task for someone with SQL skills far superior to mine! Any assistance gratefully received.

Thanks in advance

EDIT:

THe following is what I interpreted from your answer Gordon but it changes 0 rows

BEGIN TRAN

UPDATE JR
    SET DateAdded = DATEADD(MS, -3, JRFrom.DateAdded)
    FROM [OPC].[dbo].[JobRFI] JR 
    INNER JOIN [OPC].[dbo].[Job] J ON JR.JobId = J.JobId AND ProjectID = '123'
    INNER JOIN [OPC].[dbo].[RFI] R ON JR.RFIId = R.RFIId
    JOIN [OPC].[dbo].[JobRFI] JRFrom ON JR.Jobid = JRFrom.Jobid 
        AND R.RFICode = 'FR1F' 
        AND R.RFICode = 'W200'


SELECT 
        JR.JobRFIId
    ,   J.JobId
    ,   R.RFICode
    ,   R.Display
    ,   JR.DateAdded
    ,   DATEADD(MS, -3, JR.DateAdded)
  FROM [OPC].[dbo].[JobRFI] JR
  INNER JOIN [OPC].[dbo].[Job] J ON JR.JobId = J.JobId AND ProjectID = '123'
  INNER JOIN [OPC].[dbo].[RFI] R ON JR.RFIId = R.RFIId
  WHERE R.RFICode = 'W2OO' OR R.RFICode = 'FR1F'

ROLLBACK TRAN

Thanks again for your attention on this!

Upvotes: 0

Views: 58

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

Using correlated subquery:

update t
set dateadded = (
      select dateadded
      from t tf
      where t.jobid = tf.jobid
      and tf.code = 'W200')
where t.code = 'FR1F'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Presumably, you want this:

update t
    set dateAdded = tfrom.dateAdded
    from t join
         t tfrom
         on t.jobid = tfrom.jobid and
            t.Code = 'FR1F' and
            tfrom.Code = 'W200' ;

Upvotes: 1

Related Questions