do I have to DELETE then re-enter

  • Hi,

    (sorry for the dodgy title)

     

    I have 3 tables:

     

    <SCHEMA> 

    tblProduct

    productId

    productName

     

    tblProductAttribute

    productId

    attributeId

     

    tblAttribute

    attributeId

    attributeName

    <\SCHEMA>

     

    Now, in tblAttribute,  I want to change the attributeId for several attributes.However, these attributeId’s are already tied to tblProductAttribute.

     

    I really don’t want to DELETE and then re-enter all the products, just because I need to change the Id’s of a few attributes.Can T-SQL be my friend here?

     

    Cheers, 

                 Yogiberr.

  • The method depends upon whether and how you have used DRI...

    If, as your "schema" pseudocode implies, you have not used DRI, you can just use UPDATE statements on each table.  If you don't have exclusive access to the database, then update both tables within an explicit transaction.

    If you use DRI with the ON UPDATE CASCADE option, this sort of thing is easy.  Read about this in the "CREATE TABLE" topic in BOL.



    --Jonathan

  • Hi Jonathan.

    I used the "ON UPDATE CASCADE" option, and it worked, cheers.

    The reason i wanted to change the attributeId is because I wanted my query to return the results in the ORDER BY attribId:

    <code>

    SELECT DISTINCT

     p.productId,

     p.productName,  

     a.attribName,

     pa.attribValue

    FROM       

     tblProduct p,

      tblProductAttribute pa,

                 tblAttribute a

                

    WHERE p.productId = pa.productId

    AND a.attribId = pa.attribId

    AND p.categoryId =  @categoryId

    ORDER BY a.attribId  --THIS IS THE LINE I ADDED TO THE PREVIOUSLY WORKING QUERY

    <\code>

    So, it gives me an error:

    <error>

    ORDER BY items must appear in the select list if 

    SELECT DISTINCT is specified.

    <\error>

    If I remove the "SELECT DISTINCT" clause, it gives me an error at the presentation layer, where I am doing some jiggery pokery with the result sets.

    Is there any way round this?

    Essentially, I am asking if I can:

    1) keep the SELECT DISTINCT clause.

    2) ORDER By an element that does not appear in the select list.

    Do you think this is possible?

    Cheers, 

              yogiberr

  • You cannot do that because it's not logical.  Once you ask for just distinct combinations, any value not included in those combinations is not defined for the result.  How could it be?  Let's take a simple example from Northwind:

    USE Northwind

    GO

    SELECT DISTINCT o.CustomerID, s.CompanyName

    FROM Orders o JOIN Shippers s ON o.ShipVia = s.ShipperID

    Now you wish to order these by OrderDate.  As the rows being removed from the result set by using DISTINCT could have many dates both before and after other rows of the result set, it makes no sense to order by that.  If you want some arbitrary order, you could use GROUP BY and an aggregate function, e.g.:

    SELECT  p.productId, p.productName, a.attribName, pa.attribValue

    FROM  tblProduct p JOIN tblProductAttribute pa ON p.productId = pa.productId

     JOIN tblAttribute a ON pa.attribId = a.attribId

    WHERE p.categoryId =  @categoryId

    GROUP BY p.productId, p.productName, a.attribName, pa.attribValue

    ORDER BY MIN(a.AttribId)



    --Jonathan

  • brilliant,

    I added on a wee extra to the clause, so my presentation layer wouldn't have kittens.

    <code>

    ORDER BY MIN(p.productId), MIN(a.AttribId)

    <\code>

    thanks bud,

    yogiberr

     

  • Shouldn't that just be:

    ORDER BY p.ProductId, MIN(a.AttribId)

    ?



    --Jonathan

  • ok,

    i got caught up in the moment

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply