error in UPDATE statement

Database
Enthusiast

error in UPDATE statement

Hello,

can someone tell me whats wrong in this query?

update DB_UTILS.T_TABLE_C_SIZE a

set a.TABLE_SIZE = ( 

select sum(b.currentperm) from tablesize b

where b.databasename = a.databasename 

 and b.tablename = a.tablename 

 )

It returns this error:

UPDATE Failed. 3706:  Syntax error: expected something between '(' and the 'SELECT' keyword. 

Thanks!

6 REPLIES
Senior Apprentice

Re: error in UPDATE statement

You probably run that on an ancient Teradata release :-)

Scalar Subqueries like this where not allowed in Updates before TD13 

Enthusiast

Re: error in UPDATE statement

Hello Dieter, You are right, I'm using TD12.

I also tried to use this query:

update DB_UTILS.T_TABLE_C_SIZE t1

from 

( select databasename, tablename, sum(currentperm)  corrente from tablesize group by databasename, tablename ) t2

set t1.TABLE_SIZE = t2.corrente 

where t1.databasename = t2.databasename 

  and t1.tablename = t2.tablename 

but I receive:

UPDATE Failed. 3993:  Illegal usage of alias name. 

Do you think there is no solution in TD12?

Junior Supporter

Re: error in UPDATE statement

Hi.

Sorry for jumping in...

You cannot use alias on the 'SET' part of the UPDATE:

"set TABLE_SIZE = t2.corrente" should work

HTH.

Cheers.

Carlos.

Senior Apprentice

Re: error in UPDATE statement

The syntax for UPDATE FROM is a bit strange:

UPDATE t1
FROM DB_UTILS.T_TABLE_C_SIZE t1,
( select databasename, tablename, sum(currentperm) corrente from tablesize group by databasename, tablename ) t2
SET t1.TABLE_SIZE = t2.corrente
where t1.databasename = t2.databasename
and t1.tablename = t2.tablename

But you should consider switching to a MERGE instead (if you specify the target PI in ON), this should be more performant:

MERGE INTO DB_UTILS.T_TABLE_C_SIZE t1
USING
( select databasename, tablename, sum(currentperm) corrente from tablesize group by databasename, tablename ) t2
ON t1.databasename = t2.databasename
AND t1.TABLENAME = t2.TABLENAME
WHEN MATCHED THEN update
SET TABLE_SIZE = t2.corrente
Junior Supporter

Re: error in UPDATE statement

Dieter:

I stick to my guns ;-)

You cannot use table qualificator in the SET part of the update:

"SET t1.TABLE_SIZE = t2.corrente -->  SET TABLE_SIZE = t2.corrente"

Cheers.

Carlos.

Senior Apprentice

Re: error in UPDATE statement

Hi Carlos,

of course you're right :-)

I didn't read your anwer before my post and also didn't notice the bad syntax.