Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38

    Unanswered: trying to update table using WITH

    I'm usually pretty good with these UPDATE stmts, however, this one is boggling me:

    Background: I need to update a column with a value that I get from using a WITH stmt

    WITH stmt:

    Code:
    with temp as (
    select  rbt.wid_clm as old_wid,
            clm.id_clm
    from f_clm_rbt_sts rbt
    inner join d_clm clm 
        on rbt.wid_clm = clm.wid_clm
    where rbt.wid_sc_clm = 4
    )
    select  max(clm.wid_clm) as new_wid,
            temp.old_wid
    from d_clm clm
    inner join temp
        on temp.id_clm = clm.id_clm
    group by temp.old_wid)

    My assumed UPDATE stmt:
    Code:
    update f_clm_rbt_sts rbt 
    set rbt.wid_clm = (select t1.new_wid 
                             from (with temp as (
                                     select  rbt.wid_clm as old_wid,
                                               clm.id_clm
                                     from f_clm_rbt_sts rbt
                                     inner join d_clm clm 
                                      on rbt.wid_clm = clm.wid_clm
                                     where rbt.wid_sc_clm = 4
                                     )
                                     select  max(clm.wid_clm) as new_wid,
                                               temp.old_wid
                                     from d_clm clm
                                     inner join temp
                                      on temp.id_clm = clm.id_clm
                                     group by temp.old_wid
                                   ) t1
                               where rbt.wid_clm = t1.old_wid
                             )
    Can I use WITH stmts within UPDATE stmts?

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question WITH stmt

    Quote Originally Posted by bulump View Post
    I'm usually pretty good with these UPDATE stmts, however, this one is boggling me:

    Background: I need to update a column with a value that I get from using a WITH stmt

    WITH stmt:

    Code:
    with temp as (
    select  rbt.wid_clm as old_wid,
            clm.id_clm
    from f_clm_rbt_sts rbt
    inner join d_clm clm 
        on rbt.wid_clm = clm.wid_clm
    where rbt.wid_sc_clm = 4
    )
    select  max(clm.wid_clm) as new_wid,
            temp.old_wid
    from d_clm clm
    inner join temp
        on temp.id_clm = clm.id_clm
    group by temp.old_wid)

    My assumed UPDATE stmt:
    Code:
    update f_clm_rbt_sts rbt 
    set rbt.wid_clm = (select t1.new_wid 
                             from (with temp as (
                                     select  rbt.wid_clm as old_wid,
                                               clm.id_clm
                                     from f_clm_rbt_sts rbt
                                     inner join d_clm clm 
                                      on rbt.wid_clm = clm.wid_clm
                                     where rbt.wid_sc_clm = 4
                                     )
                                     select  max(clm.wid_clm) as new_wid,
                                               temp.old_wid
                                     from d_clm clm
                                     inner join temp
                                      on temp.id_clm = clm.id_clm
                                     group by temp.old_wid
                                   ) t1
                               where rbt.wid_clm = t1.old_wid
                             )
    Can I use WITH stmts within UPDATE stmts?
    For this updateyou don't need to use WITH stmt

    Lenny

  3. #3
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38
    maybe it's a Monday....but I'm drawing a blank here

  4. #4
    Join Date
    Oct 2009
    Location
    Calgary, AB Canada
    Posts
    38
    This works....

    Code:
    merge into f_clm_rbt_sts a
    using (select	max(t1.wid_clm) as max_wid, 
    				t2.wid_clm, 
    				t1.id_clm 
    		from d_clm t1 
    		inner join (select	rbt.wid_clm, 
    								clm.id_clm 
    					 from f_clm_rbt_sts rbt 
    					 inner join d_clm clm 
    						on rbt.wid_clm = clm.wid_clm 
    					 where rbt.wid_sc_clm = 4) t2 
    				on t1.id_clm = t2.id_clm group by t2.wid_clm, t1.id_clm
    	   ) b
    on a.wid_clm = b.wid_clm
    when matched and a.wid_sc_clm = 4 then
    update set a.wid_clm = b.max_wid

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This will work too.....

    Code:
    UPDATE f_clm_rbt_sts a
       SET wid_clm
           = (SELECT MAX(t1.wid_clm)
                FROM d_clm t1
                INNER JOIN
                     d_clm t2
                 ON  t2.id_clm  = t1.id_clm
               WHERE t2.wid_clm = a.wid_clm
             )
     WHERE wid_sc_clm = 4
    ;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Background: I need to update a column with a value that I get from using a WITH stmt

    WITH stmt:

    Code:
    with temp as (
    select  rbt.wid_clm as old_wid,
            clm.id_clm
    from f_clm_rbt_sts rbt
    inner join d_clm clm 
        on rbt.wid_clm = clm.wid_clm
    where rbt.wid_sc_clm = 4
    )
    select  max(clm.wid_clm) as new_wid,
            temp.old_wid
    from d_clm clm
    inner join temp
        on temp.id_clm = clm.id_clm
    group by temp.old_wid)
    WITH will be not neccessary, like this:
    Code:
    SELECT MAX(clm2.wid_clm) AS new_wid
         , rbt.wid_clm       AS old_wid
      FROM f_clm_rbt_sts rbt
      JOIN d_clm         clm1
       ON  clm1.wid_clm = rbt.wid_clm
      JOIN d_clm         clm2
       ON  clm2.id_clm  = clm1.id_clm
     WHERE rbt.wid_sc_clm = 4
     GROUP BY
           rbt.wid_clm
    ;
    Last edited by tonkuma; 12-15-09 at 20:41.

Posting Permissions

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