trying to update table using WITH

bulump · #1 (**permalink**) 12-14-09, 13:40

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?

Lenny77 · #2 (**permalink**) 12-14-09, 14:01

Quote:

Originally Posted by bulump

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

bulump · #3 (**permalink**) 12-14-09, 15:53

maybe it's a Monday....but I'm drawing a blank here

bulump · #4 (**permalink**) 12-14-09, 17:28

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

tonkuma · #5 (**permalink**) 12-15-09, 08:37

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
;

tonkuma · #6 (**permalink**) 12-15-09, 19:24

Quote:

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech