All Forums Database
Cesco 13 posts Joined 02/13
24 Jul 2014
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!

dnoeth 4628 posts Joined 11/04
24 Jul 2014

You probably run that on an ancient Teradata release :-)
Scalar Subqueries like this where not allowed in Updates before TD13 

Dieter

Cesco 13 posts Joined 02/13
24 Jul 2014

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?

CarlosAL 512 posts Joined 04/08
24 Jul 2014

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.

dnoeth 4628 posts Joined 11/04
24 Jul 2014

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

 

Dieter

CarlosAL 512 posts Joined 04/08
25 Jul 2014

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.

dnoeth 4628 posts Joined 11/04
25 Jul 2014

Hi Carlos,
of course you're right :-)
I didn't read your anwer before my post and also didn't notice the bad syntax.

Dieter

You must sign in to leave a comment.