SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help with update and select

    Hello,

    I have a table caxnode which has fields node_alias, node_mode, node_id, etc..

    In some cases, the node_alias is marked as "regular" as well as 'logical' for different node_id's. I want to change the entries for which the same node_alias is marked 'regular' as well as 'logical' to logical. i.e. change the "regular" to "logical" if the same entry is marked 'logical' in the table and is an LDOM partition ..

    This is my working query to return the results that I am interested in changing..

    select Node_ID, Node_type, Num_of_proc, Node_Alias, Host_ID, Node_mode, Partition_Type from CAXNODE nd where (Node_mode = 'VIRTUAL' or Node_mode = 'REGULAR') and Node_Alias in (select nd2.Node_Alias from CAXNODE nd2 where nd2.Node_mode = 'LOGICAL' and Partition_Type = 'LDOM' ) ;

    Now I want to change the values to set Node_mode to 'LOGICAL'; Partition_Type to 'LDOM', Host_ID to (nd2.Host_ID); Num_of_proc to (nd2.Num_of_proc);

    How can I do this in one update statement?

  2. #2
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here's what i am looking for

    my table is as follows

    id alias host_id node_type num_procs

    1 abc abc regular 2
    2 abc xyz LDOM 4
    3 def def virtual 2
    4 def ppp LDOM 8
    5 abc abc virtual 3


    I want those alias that are marked as LDOM's to be replaced with the LDOM node_type and the num proc's of the LDOM

    id alias host_id node_type num_procs
    1 abc xyz LDOM 4
    2 abc xyz LDOM 4
    3 def ppp LDOM 8
    4 def ppp LDOM 8
    5 abc xyz LDOM 4


    Can someone tell me an update query for this?

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After quickly checking your situation:

    To execute what you want in single query you have to write subquery and problem is that you can not update table which you used in from with select in sub query. So overall its not that much easy to achieve what you want. Rather if you write batch procedure or something than might be. I would suggest separate out your process in two separate query if possible. if not than let me know i will try bit more

    See that discussion http://stackoverflow.com/questions/4...in-from-clause

    This might help you.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    tejash, i thought so too, however, this isn't mysql, it's microsoft access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its access for me, customer might run it on oracle, but the same sql should work.. right? i've reproduced the situation in access..

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nkshirsa View Post
    ... but the same sql should work.. right?
    no, not necessarily

    unfortunately, sql can vary significantly between database systems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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