SQL Update multiple rows with value from another row

Question

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!

Edit the question and show the results that you want.

Gordon Linoff
– Gordon Linoff

2018-06-23 11:57:58 +00:00
Commented Jun 23, 2018 at 11:57 — Gordon Linoff
– Gordon Linoff, Commented Jun 23, 2018 at 11:57

Gordon Linoff · Accepted Answer · 2018-06-23 11:59:33Z

1

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' ;

answered Jun 23, 2018 at 11:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ledragon Over a year ago

Apologies Gordon I didn't as the right question. Have edited above. Thanks for the answer!

Gordon Linoff Over a year ago

@ledragon . . . Doesn't this do what you want?

ledragon Over a year ago

Hi Gordon, sadly I can't get it to work. I've edited the question to show what I tried. Your help is much appreciated!

Kamil Gosciminski · Accepted Answer · 2018-06-23 12:01:04Z

1

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'

answered Jun 23, 2018 at 12:01

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

3 Comments

ledragon Over a year ago

Apologies Kamil I didn't as the right question. Have edited above. Thanks for the answer!

Kamil Gosciminski Over a year ago

Why don't you use the answer itself? Your query is invalid. Here: AND R.RFICode = 'FR1F' AND R.RFICode = 'W200'. RFICode can't be both values at once.

ledragon Over a year ago

Hi Kamil, I initally asked the wrong question as Code is in a seperate table...I'm floundering as to how to interpret the answer to make the change I need

Collectives™ on Stack Overflow

SQL Update multiple rows with value from another row

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related