using update with WITH clause

Database
Enthusiast

using update with WITH clause

Hi,

I'm trying to do something like below, but its giving me syntax error,

can anyone please tell me what is the issue and how to fix this.

with cte0(ID,int_col1) as(

    select row_number() over (order by (select 1)) AS ID,int_col1 from shuffletest

)

update cte0 set int_col1=ID;

But when i run following query it works.

with cte0(ID,int_col1) as(

    select row_number() over (order by (select 1)) AS ID,int_col1 from shuffletest

)

select * from cte0;

Regards,

Parth Malhan

3 REPLIES
Senior Apprentice

Re: using update with WITH clause

Hi Parth,

you simply can't update a Common Table Expression in Teradata.

What are you trying to achieve, maybe there's a workaround.

Enthusiast

Re: using update with WITH clause

Hi,

I have 2 tables having same number of rows(UNIQUE).

1 table is having identity column and 2nd doesn't have.

I want to update column in 2nd table with 1st table(any row from 1st table)

BUT WE CAN'T USE any ROW TWICE.

Regards,

Parth Malhan

Senior Apprentice

Re: using update with WITH clause

Hi Parth,

randomly updating a table? Why?

Is there no key?

Can you show the actual definition?

What's the size of those tables?

The only way might be inserting the data into a new table and then drop/rename...