May 18, 2017 at 9:16 am
Hi,
I have table as follows
KeywordID Keyword IsCoreKeyword keywordorder adgroupid
----------- ------------------------- ------------- ------------ ----------
37 4x4 service 1 1 270
38 4x4 servicing 0 2 NULL
39 4X4 car service 0 3 NULL
40 4X4 service centre 0 4 NULL
41 4x4 garage 1 5 456
42 4X4 service cost 1 6 356
43 4X4 servicing cost 0 7 NULL
44 4X4 part 1 8 123
45 4X4 spare 0 9 NULL
46 4x4 repair 1 10 145
47 Land rover service 1 11 532
48 Land Rover Servicing 0 12 NULL
49 Land rover service centre 0 13 NULL
50 Range rover service 1 14 324
51 Range rover servicing 0 15 NULL
52 Range rover service centr 0 16 NULL
53 4X4 mechanic 1 17 533
54 4X4 mot 1 18 119
I need help to write a query to populate the missing adgroupid fields (so where they are equal to null). The row gets its adgroup id from the row where IsCore = 1 and it has a lower KeywordOrder.
So in the above example KeywordID 38, 39 and 40 would be assigned the adgroupid of 270. KeywordID 43 would be get adgorupid 356 , keywordid 48 an d49 would get adgorupid = 532 etc
This is a sort of recursive query but i am unable to figure this out. Any help would be greatly appreciated
May 18, 2017 at 9:18 am
Please post DDL and sample data for quicker assistance.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 18, 2017 at 9:28 am
Henrico Bekker - Thursday, May 18, 2017 9:18 AMPlease post DDL and sample data for quicker assistance.
Here is the table structure and the sample data
CREATE TABLE [dbo].[Keyword](
[KeywordID] [int] NULL,
[Keyword] [varchar](25) NULL,
[IsCoreKeyword] [bit] NULL,
[KeywordOrder] [int] NULL,
[AdgroupID] [nchar](10) NULL
) ON [PRIMARY]
insert into keyword select 37,'4x4 service',1,1,270
insert into keyword select 38,'4x4 servicing',0,2,NULL
insert into keyword select 39,'4X4 car service',0,3,NULL
insert into keyword select 40,'4X4 service centre',0,4,NULL
insert into keyword select 41,'4x4 garage',1,5,456
insert into keyword select 42,'4X4 service cost',1,6,356
insert into keyword select 43,'4X4 servicing cost',0,7,NULL
insert into keyword select 44,'4X4 part',1,8,123
insert into keyword select 45,'4X4 spare',0,9,NULL
insert into keyword select 46,'4x4 repair',1,10,145
insert into keyword select 47,'Land rover service',1,11,532
insert into keyword select 48,'Land Rover Servicing',0,12,NULL
insert into keyword select 49,'Land rover service centre',0,13,NULL
insert into keyword select 50,'Range rover service',1,14,324
insert into keyword select 51,'Range rover servicing',0,15,NULL
insert into keyword select 52,'Range rover service centr',0,16,NULL
insert into keyword select 53,'4X4 mechanic',1,17,533
insert into keyword select 54,'4X4 mot',1,18,119
go
May 18, 2017 at 9:32 am
This works for your data:UPDATE Keyword
SET AdgroupID = (SELECT TOP 1 AdgroupID
FROM Keyword kw
WHERE kw.KeywordID <= keyword.KeywordID
AND kw.IsCoreKeyword = 1
ORDER BY kw.KeywordID DESC)
WHERE AdgroupID IS NULL;
Edit:Fix the tabbing. >_<K
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2017 at 9:38 am
Thom A - Thursday, May 18, 2017 9:32 AMThis works for your data:UPDATE Keyword
SET AdgroupID = (SELECT TOP 1 AdgroupID
FROM Keyword kw
WHERE kw.KeywordID <= keyword.KeywordID
AND kw.IsCoreKeyword = 1
ORDER BY kw.KeywordID DESC)
WHERE AdgroupID IS NULL;Edit:Fix the tabbing. >_<K
Perfect, that works great. Thanks for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply