1

I have a table in SQL Server with some null values in column "date":

platform   date         id
---------------------------
web        2018-10-10   1
mob                     1
mob                     1
web        2018-10-15   2
mob                     2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob                     3

I want to update null values in 'date' for 'mob' platform by matching the 'id' column from platform 'web'. The result should look like this:

platform   date         id
---------------------------
web        2018-10-10   1
mob        2018-10-10   1
mob        2018-10-10   1
web        2018-10-15   2
mob        2018-10-15   2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob        2018-10-12   3

Will really appreciate your help!

1
  • MySQL is not the same as SQL Server. Commented Oct 18, 2018 at 20:16

5 Answers 5

2

You can use an updatable CTE:

with toupdate as (
      select t.*, max(date) over (partition by id) as max_date
      from t
     )
update toupdate
    set date = max_date
    where date is null;
Sign up to request clarification or add additional context in comments.

Comments

0
update a
SET a.date = b.date
from #test AS a
INNER JOIN (SELECT * FROM #test WHERE platform = 'web') as b on a.id = b.id
WHERE a.date is null

Update the tablename #test as needed.

Comments

0

A correlated subquery should work

declare @table table (platform char(3), [date] date, id int)
insert into @table
values
('web','2018-10-10',1),
('mob',null,1),
('mob',null,1),
('web','2018-10-15',2),
('mob',null,2),
('ntl','2018-10-09',3),
('web','2018-10-12',3),
('web','2018-10-11',4),
('mob',null,3)

update @table
set date = (select max(date) from @table t2 where t2.id = [@table].id)
where [@table].date is null  

select * from @table

2 Comments

I created a temp table with the author's sample data, this does not give the desired result.
That's because the guy who edited it messed it up.... want to check that out now @zkemppel?
0

I would not recommend naming a column date as it's a reserved word, but this will give you the desired result assuming there is only 1 web entry per id.

UPDATE a
SET [date] = b.[date]
FROM MyTable a
INNER JOIN MyTable b ON a.id = b.id and b.platform = 'web'
WHERE a.platform = 'mob' AND a.[date] IS NULL

enter image description here

9 Comments

@scsimon yes it will, take a look at the screenshot I just added.
Add another null with id 3
And what if web wasn’t the last date in the order? They never said that it would be.
That is irrelevant, the join will work as is regardless of order.
Sure you’ll just pull a random date. And that is relevant sir 😜
|
0

You can do like

UPDATE T
SET [Date] = D
FROM
(
    SELECT ID, MAX([Date]) AS D
    FROM T
    WHERE [Date] IS NOT NULL
    GROUP BY ID
) TT INNER JOIN T ON T.ID = TT.ID
WHERE T.[Date] IS NULL;

db<>fiddle demo

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.