update from cte select

Question

I need to update a table tb0 based on previous CTE statements. The code is similar to the pseudo-code bellow:

with tb1 as(select tb_index from tb0 where tb_index=10),

tb2 as (select tb_index,count(orders))total from orders inner join tb1 on tb1.tb_index=orders.tb_index)

update tb0 set tb0.status='a'
from tb2
inner join tb0.tb_index=tb2.tb_index
where
tb2.total>30

question is, will the update above work considering the inner join and where clause ?

It depends on which dbms you're using. That code isn't ANSI SQL compliant. — jarlh, Aug 26 '20 at 19:15
Please tag your question with the database you are running: mysql, oracle, sql-server...? Also, what is the purpose of this piece of code? — GMB, Aug 26 '20 at 21:10
No it won't work, because Firebird's SQL syntax of `UPDATE` doesn't support `FROM`, nor CTEs (Firebird only supports CTEs on select expressions). — Mark Rotteveel, Aug 27 '20 at 06:11
Hello Mark, you are correct. It is not working so I tried another approach. Tks — Jonathan Livingston Seagull, Aug 27 '20 at 13:23

score -2 · Answer 1 · answered Aug 26 '20 at 19:32

-2

Way:-1

WITH TB1 AS(SELECT TB_INDEX FROM TB0 WHERE TB_INDEX=10),
TB2 AS (SELECT TB_INDEX,COUNT(ORDERS))TOTAL FROM ORDERS INNER JOIN TB1 ON TB1.TB_INDEX=ORDERS.TB_INDEX)
UPDATE T SET T.STATUS='A'
FROM TB0 T WHERE EXISTS (SELECT 1 FRM TB2 WHERE T.TB_INDEX=TB2.TB_INDEX
TB2.TOTAL>30)

Way:-2

WITH TB1 AS(SELECT TB_INDEX FROM TB0 WHERE TB_INDEX=10),
TB2 AS (SELECT TB_INDEX,COUNT(ORDERS))TOTAL FROM ORDERS INNER JOIN TB1 ON TB1.TB_INDEX=ORDERS.TB_INDEX)
UPDATE T SET T.STATUS='A'
FROM TB2,TB0 T WHERE T.TB_INDEX=TB2.TB_INDEX
TB2.TOTAL>30

answered Aug 26 '20 at 19:32

Somendra Kanaujia

727
5
12

Firebird's SQL dialect doesn't have a `FROM` clause in `UPDATE` (nor does the SQL standard syntax IIRC). – Mark Rotteveel Aug 27 '20 at 06:09
You are right, It doesn't work – Jonathan Livingston Seagull Aug 27 '20 at 13:24

update from cte select

1 Answers1