Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Need help with an UPDATE query

    Hi,

    I'm trying to get a table updated from a select statement but MS SQL isn't having any of it. Could some one tell me what's wrong with the following SQL Query?

    UPDATE MainTable
    set (ID,PeriodID,Visitors,SalesPerVisitor) =
    (SELECT ID,PeriodID,Visitors,SalesPerVisitor
    FROM Period2
    WHERE PeriodID not In (select PeriodID FROM Period2))

    Thanks in advance.

    Doogal

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi Doogal,

    if I got it right, you will never get corresponding results from the query

    SELECT ID,PeriodID,Visitors,SalesPerVisitor
    FROM Period2
    WHERE PeriodID not In (select PeriodID FROM Period2)

    because there will be no PeriodID in table Period2 which is not in table Period2. (Maybe you want to select a PeriodID from another table?)

    Therefore, your UPDATE-statement dows not update any lines.

    If other questions appear, please post a reply!

    Greetings,
    Carsten

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    Hi Carsten

    Ooops, the last bit should have read "not In (select PeriodID FROM Period1))" So the query should have been:

    UPDATE MainTable
    set (ID,PeriodID,Visitors,SalesPerVisitor) =
    (SELECT ID,PeriodID,Visitors,SalesPerVisitor
    FROM Period2
    WHERE PeriodID not In (select PeriodID FROM Period1))

    This currently gives me a syntax error with the first "(".

    Thanks,
    Doogal

  4. #4
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi Doogal!

    Think the syntax should be like this:

    UPDATE MainTable
    set ID = tmp.ID
    ,PeriodID = tmp.PeriodID
    ,Visitors = tmp.Visitors
    ,SalesPerVisitor = tmp.SalesPerVisitor
    FROM
    (SELECT ID,PeriodID,Visitors,SalesPerVisitor
    FROM Period2
    WHERE PeriodID not In (select PeriodID FROM Period2) as tmp )

    Greeting,
    Carsten

  5. #5
    Join Date
    Sep 2003
    Posts
    3
    Hi Carsten

    That's great, thanks! That'll teach me to try and use an Oracle example!

    Thanks again,
    Doogal

  6. #6
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    I think, your Oracle example would have worked in DB2 also!
    :-)

    Carsten

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •