Solved

Update table based on nested select from the same table

Posted on 2006-07-12
7
361 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:snavebelac
  • 4
  • 2
7 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17089686
try this


update tbl_FPCat set ParentID = b.categoryid
from tbl_FPCat b
where b.XMLCode = tbl_FPCat.XMLParent
go
0
 
LVL 8

Expert Comment

by:Kobe_Lenjou
ID: 17089749
UPDATE    tblup
SET              tblup.ParentID = (select CategoryID from tblup T where T.XMLParent  = tblup.xmlcode)
WHERE     (tblup.XMLCode <> NULL) OR (tblup.XMLCode <> '')
0
 
LVL 6

Author Comment

by:snavebelac
ID: 17089761
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:snavebelac
ID: 17089858
that last respnse was to imran_fast
0
 
LVL 6

Author Comment

by:snavebelac
ID: 17089919
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
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 17090015
try this


update a set a.ParentID = b.categoryid
from tbl_FPCat a
inner join tbl_fpcat b on
b.XMLCode = a.XMLParent
go
0
 
LVL 6

Author Comment

by:snavebelac
ID: 17091371
imran_fast

That worked great - thanks - knew it had to be simple

C
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question