skahlert2010
asked on
update statement based on "with clause" returns error
Hello pros! I have a short question as I am stuck with a problem.
I have a query based on a with clause and would like to use it for an update statement.
I recieve the error message that an Update query must include a subquery.
Can you give me some help please or ist this type of construction not working at all?
Brgds,
Seb
I have a query based on a with clause and would like to use it for an update statement.
I recieve the error message that an Update query must include a subquery.
Can you give me some help please or ist this type of construction not working at all?
Brgds,
Seb
update tbl_matrix_custom_eval f SET (f.ba_kat, f.bemerkung) =
with w as ((select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from
vt_tbl_bestand v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart)
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
group by
b.ba_lang,
x.lng_baumart,
x.ba_kat,
ba_gruppe,
x.flag)
union
(select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from
vt_tbl_verjuengung v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart)
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
))
-- This is the subquery for the update
(select ba_kat, bemerkung from (
((select w.lng_baumart, w.ba_kat, w.ba_gruppe, w.flag, w.disable_status, null bemerkung from w)
union all
(select d.lng_baumart, d.ba_kat,
decode(r.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, d.flag,
case when d.lng_baumart = 78 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart = 62) then 1
when d.lng_baumart = 74 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (5,6,7,8,9,81)) then 1
when d.lng_baumart = 72 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (22,23,24,25,26,27)) then 1
when d.lng_baumart = 71 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (46,47)) then 1
when d.lng_baumart = 54 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (13,14,15)) then 1
when d.lng_baumart = 53 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (28,29,30,31,32,33,79)) then 1
else null end as disable_status, 'Diese BA ist LRT-typisch, jedoch nicht im LRT vertreten!' bemerkung
from vt_tbl_baumart r, vt_tbl_matrix d
where
r.cnt_baumart = d.lng_baumart
and (d.lrt_class = :p2008_str_lrt_mx)
and d.ba_kat in ('S','B','H','P','N') and d.lng_baumart not in (select
w.lng_baumart from w))))z) where z.lng_baumart = f.lng_baumart and f.lng_gebiet = :p2008_cnt_gebiet and f.lrt_class = :p2008_str_lrt and f.int_wg = :p2008_wg and f.int_be = :p2008_be and eval_type = 1;
You can't use the with query in both the set and where clause as they are different queries and are therefore not related/connected.
For example, if you take a simple update query:
update mytable
set col1 = (select x from mytable2 where a = b)
where exists (select y from mytable3 where z = x);
In this case the 2 sub queries and completeley independant of each other. You can't refer to table mytable2 in the sub query in the where clause and likewise you can't refer to mytable3 in the sub query in the set clause.
The same applies to defining a sub query using the with clause, you can reference the named query in a query that is part of another clause in the update statement.
Hope this is clear.
For example, if you take a simple update query:
update mytable
set col1 = (select x from mytable2 where a = b)
where exists (select y from mytable3 where z = x);
In this case the 2 sub queries and completeley independant of each other. You can't refer to table mytable2 in the sub query in the where clause and likewise you can't refer to mytable3 in the sub query in the set clause.
The same applies to defining a sub query using the with clause, you can reference the named query in a query that is part of another clause in the update statement.
Hope this is clear.
ASKER
Well I wrote several fully functional insert statements where the with as declaration followed the insert command.
I just modified my code as you suggested and receive an error: "Keyword SELECT is missing"!
No line is mentioned in my debugger.
Any idea?
I just modified my code as you suggested and receive an error: "Keyword SELECT is missing"!
No line is mentioned in my debugger.
Any idea?
with w as ((select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from
vt_tbl_bestand v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart)
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
group by
b.ba_lang,
x.lng_baumart,
x.ba_kat,
ba_gruppe,
x.flag)
union
(select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from
vt_tbl_verjuengung v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart)
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
))
update tbl_matrix_custom_eval f SET (f.ba_kat, f.bemerkung) =
-- This is the subquery for the update
(select ba_kat, bemerkung from (
((select w.lng_baumart, w.ba_kat, w.ba_gruppe, w.flag, w.disable_status, null bemerkung from w)
union all
(select d.lng_baumart, d.ba_kat,
decode(r.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, d.flag,
case when d.lng_baumart = 78 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart = 62) then 1
when d.lng_baumart = 74 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (5,6,7,8,9,81)) then 1
when d.lng_baumart = 72 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (22,23,24,25,26,27)) then 1
when d.lng_baumart = 71 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (46,47)) then 1
when d.lng_baumart = 54 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (13,14,15)) then 1
when d.lng_baumart = 53 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (28,29,30,31,32,33,79)) then 1
else null end as disable_status, 'Diese BA ist LRT-typisch, jedoch nicht im LRT vertreten!' bemerkung
from vt_tbl_baumart r, vt_tbl_matrix d
where
r.cnt_baumart = d.lng_baumart
and (d.lrt_class = :p2008_str_lrt_mx)
and d.ba_kat in ('S','B','H','P','N') and d.lng_baumart not in (select
w.lng_baumart from w))))z) where z.lng_baumart = f.lng_baumart and f.lng_gebiet = :p2008_cnt_gebiet and f.lrt_class = :p2008_str_lrt and f.int_wg = :p2008_wg and f.int_be = :p2008_be and eval_type = 1;
The WITH clause can only be used with SELECT statements so you are correct to preceed the SELECT statement with it as the update statement wouldn't understand the WITH clause.
ASKER
Thanks milleniumaire!
If I understood you correctly there's no way I can use the with clause for my demand? Is that correct?
If I understood you correctly there's no way I can use the with clause for my demand? Is that correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your info! Helps a lot! I won't waste more time on that with clause query!
think define the vuew then use it
with w as (....)
update...