Update statement

Database
Enthusiast

Update statement

Hi, I am new to Teradata and was going through Update statements here:     http://info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch03.034...

 

and made my own, like this:

 

UPDATE T1 AS A FROM T2  AS B SET col1 = col1 * 1.05 WHERE A.ID = B.ID AND ID = 1003;

 

And I get this error: 'Illegal usage of alias name'. 

 

What am I missing?

Tags (3)

Accepted Solutions
Junior Apprentice

Re: Update statement

Hi,

 

I don't know about videos etc but there are numerous web sites which will provide introductory SQL tutorials.

 

Hopefully the following will give you an idea of what is happening in Teradata.

 

Firstly, remember that the rules of SQL joins mean that processing affects every row in table#1 every time it joins to a row in table#2.

 

In a SELECT this is easy to see and understand, the following example shows this.

Table#1
Col1 Col2
1    a
2    b

Table#2
Col1 Col2
1    c
1    d

If we run the following SELECT:

SELECT t1.col1
   ,t1.col2
   ,t2.col2
FROM t1
INNER JOIN t2
  ON t1.col1 = t2.col1

We get the following result set:

t1.col1 t1.col2 t2.col2
1       a       c
1       a       d

Although there is only 1 row in table#1 with col1=1 it appears twice in the answer set because that row joins to two rows in table#2.

 

When Teradata executes an UPDATE statement that joins tables it builds a temporary file (called a SPOOL file) which contains the new version of any rows that will be updated. This SPOOL file is the result of joining the two tables on the join condition- which means it can contain two copies of a 'target row' (i.e. a row in the target table - the one being updated).

 

When this happens and Teradata tries to apply the new version of the rows to the target table, if it finds the same target row mentioned more than once in that SPOOL file you'll get the message that you referred to ('Target row updated by multiple source rows').

 

This happens because of the cardinality of rows in the two tables based on the join condition - basically you've got a 1:M relationship between the rows in table#1 and those in table#2.

 

To avoid the problem you'll typically need to use a sub-query to create a 1:1 relationship and then join the target table with the results of the sub-query.

 

I hope that helps.

 

Cheers,

Dave

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
5 REPLIES
Junior Apprentice

Re: Update statement

Hi,

 

The following is valid syntax:

UPDATE T1 FROM T2  AS B 
   SET col1 = t1.col1 * 1.05 
WHERE t1.ID = B.ID 
  AND t1.ID = 1003;

If you look at the syntax diagram(s) for UPDATE the AS clause is only allowed on one table. Your original code had it on both tables in the query.

 

I think you'll  find that the example you came across is not valid.

 

Cheers,

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Apprentice

Re: Update statement

Just checked some other examples in this area and the following is valid syntax. Note that it uses aliases on both tables (so my comment above isn't quite correct) but in a different form.

 

UPDATE e FROM employee AS e
     ,department AS d
     SET salary_amount = salary_amount * 1.05
 WHERE e.department_number = d.department_number
   AND salary_amount > 25000;

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Update statement

I simply replicated the scenario given in the documentation, and the example had aliases for both the tables'. Anyway, i faced a new issues, saying: 'Target row updated by multiple source rows'. Can you please suggest me some videos or something with simply explains how the Update query executes in all possible scenario? Much appriciate your help :)

Junior Apprentice

Re: Update statement

Hi,

 

I don't know about videos etc but there are numerous web sites which will provide introductory SQL tutorials.

 

Hopefully the following will give you an idea of what is happening in Teradata.

 

Firstly, remember that the rules of SQL joins mean that processing affects every row in table#1 every time it joins to a row in table#2.

 

In a SELECT this is easy to see and understand, the following example shows this.

Table#1
Col1 Col2
1    a
2    b

Table#2
Col1 Col2
1    c
1    d

If we run the following SELECT:

SELECT t1.col1
   ,t1.col2
   ,t2.col2
FROM t1
INNER JOIN t2
  ON t1.col1 = t2.col1

We get the following result set:

t1.col1 t1.col2 t2.col2
1       a       c
1       a       d

Although there is only 1 row in table#1 with col1=1 it appears twice in the answer set because that row joins to two rows in table#2.

 

When Teradata executes an UPDATE statement that joins tables it builds a temporary file (called a SPOOL file) which contains the new version of any rows that will be updated. This SPOOL file is the result of joining the two tables on the join condition- which means it can contain two copies of a 'target row' (i.e. a row in the target table - the one being updated).

 

When this happens and Teradata tries to apply the new version of the rows to the target table, if it finds the same target row mentioned more than once in that SPOOL file you'll get the message that you referred to ('Target row updated by multiple source rows').

 

This happens because of the cardinality of rows in the two tables based on the join condition - basically you've got a 1:M relationship between the rows in table#1 and those in table#2.

 

To avoid the problem you'll typically need to use a sub-query to create a 1:1 relationship and then join the target table with the results of the sub-query.

 

I hope that helps.

 

Cheers,

Dave

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Update statement

Oh yes! That comma after the first column is a join! Thanks a lot Dave :) Actually the scenario is such, I need to make a certain query work in its' format. It's more 'query related' than output related.