snavebelac
asked on
Update table based on nested select from the same table
I have a table of categories filled with some of our own data and data from an XML feed. I have no access to the original data from the XML feed.
Data looks something like this at the moment
CategoryID | CatName | ParentID | XMLCode | XMLParent
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------
1 Cars 0 1234 0
2 Bikes 0 9999 0
3 Mirrors 0 19673 1234
4 Handlebars 0 96734 9999
5 Cheese 0 <NULL> <NULL>
6 French 5 <NULL> <NULL>
Each category can have a parent of another category so the eventual structure is a tree like listing. The categories from the XML feed will get updated semi-regularly in case any get added or changed etc... I need to be able to map the parentIDs for the XML categories based on the XMLCode and XMLParent.
Using the example above The ParentID For "Mirrors" would get set to "1" and the ParentID for "handlebars" would get set to "2". These are arrived at using the XMLParent which are already present from the XML feed. The "Cheese" and "French" records would be ignored as the XMLdata is not present.
Using this forum and other resources I have come up with the following which runs but does nothing to the data. The table is called tbl_FPCat.
UPDATE tblup
SET tblup.ParentID = nested.CategoryID
FROM tbl_FPCat AS tblup JOIN
(SELECT CategoryID, XMLCode, XMLParent
FROM tbl_FPCat
WHERE XMLCode= XMLParent) AS nested ON tblup.XMLParent = nested.XMLCode
WHERE (tblup.XMLCode <> NULL) OR (tblup.XMLCode <> '')
I dont even know if this is possible or even if I am on the right track at this point. Any help greatly appreciated. Anything not clear - just ask.
C
Data looks something like this at the moment
CategoryID | CatName | ParentID | XMLCode | XMLParent
--------------------------
1 Cars 0 1234 0
2 Bikes 0 9999 0
3 Mirrors 0 19673 1234
4 Handlebars 0 96734 9999
5 Cheese 0 <NULL> <NULL>
6 French 5 <NULL> <NULL>
Each category can have a parent of another category so the eventual structure is a tree like listing. The categories from the XML feed will get updated semi-regularly in case any get added or changed etc... I need to be able to map the parentIDs for the XML categories based on the XMLCode and XMLParent.
Using the example above The ParentID For "Mirrors" would get set to "1" and the ParentID for "handlebars" would get set to "2". These are arrived at using the XMLParent which are already present from the XML feed. The "Cheese" and "French" records would be ignored as the XMLdata is not present.
Using this forum and other resources I have come up with the following which runs but does nothing to the data. The table is called tbl_FPCat.
UPDATE tblup
SET tblup.ParentID = nested.CategoryID
FROM tbl_FPCat AS tblup JOIN
(SELECT CategoryID, XMLCode, XMLParent
FROM tbl_FPCat
WHERE XMLCode= XMLParent) AS nested ON tblup.XMLParent = nested.XMLCode
WHERE (tblup.XMLCode <> NULL) OR (tblup.XMLCode <> '')
I dont even know if this is possible or even if I am on the right track at this point. Any help greatly appreciated. Anything not clear - just ask.
C
UPDATE tblup
SET tblup.ParentID = (select CategoryID from tblup T where T.XMLParent = tblup.xmlcode)
WHERE (tblup.XMLCode <> NULL) OR (tblup.XMLCode <> '')
SET tblup.ParentID = (select CategoryID from tblup T where T.XMLParent = tblup.xmlcode)
WHERE (tblup.XMLCode <> NULL) OR (tblup.XMLCode <> '')
ASKER
thanks for the response
when I run that I get the following error
"The column prefix 'tbl_FPCat' does not match with a table name or alias name used in the query."
which is odd... I checked the names of everything and it is all correct any thoughts?
C
when I run that I get the following error
"The column prefix 'tbl_FPCat' does not match with a table name or alias name used in the query."
which is odd... I checked the names of everything and it is all correct any thoughts?
C
ASKER
that last respnse was to imran_fast
ASKER
Kobe_Lenjou
I tried that configuration once before - I tried again and I get this error...
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
the XMLCode column is unique apart from the NULLs and zero length strings which are being filtered.
Any ideas ?
Thanks
C
I tried that configuration once before - I tried again and I get this error...
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
the XMLCode column is unique apart from the NULLs and zero length strings which are being filtered.
Any ideas ?
Thanks
C
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
imran_fast
That worked great - thanks - knew it had to be simple
C
That worked great - thanks - knew it had to be simple
C
update tbl_FPCat set ParentID = b.categoryid
from tbl_FPCat b
where b.XMLCode = tbl_FPCat.XMLParent
go