Could somebody please help me out with UPDATE-FROM ?

My table 'Rates' has a structure:

currency_id_1 int
currency_id_2 int
rate float
flag boolean

I'm trying to recalculate rates for all the entries marked by flag=1
using the rates from THE SAME table:

update Rates set rate = t1.rate * t2.rate from Rates t1, Rates t2
where currency_id_1 = t1.currency_id_1
and t1.currency_id_2 = t2.currency_id_1
and t2.currency_id_2 = currency_id_2
and flag = 1

Sybase ASE 12.5 complains with:

Ambiguous column name currency_id_1

What a problem here? How to bypass that (without temporary tables)?

Thanks,
Aleksey

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Do you know that ISO has currency code standards? That you never use
FLOAT for commercial calculations (look up the rules for Euro
conversion)? That BOOLEAN is not Standard SQL?

CREATE TABLE Rates
(currency_code_1 CHAR(3) NOT NULL,
currency_code_2 CHAR(3) NOT NULL,
rate DEIMAL(12,4) NOT NULL);
flag INTEGER NOT NULL,
PRIMARY KEY (currency_code_1, currency_code_2));


The Standard UPDATE statement does not use a FROM clause. It makes no
sense.

Your code should use a scalar subquery, something like this -- I
cannot figure out what you are trying to do by reading your code and
your spec is not clear.

UPDATE Rates
SET rate
= rate
* (SELECT T1.rate
FROM Rates AS T1
WHERE Rates.currency_id_1 = T1.currency_id_2)
WHERE flag = 1;

Alex wrote:
Is it not just that when you reference currency_id_1 in the where clause
you don't always specify the table?

So it should be :
where t2.currency_id_1 = t1.currency_id_1 <-- t2 added here
and t1.currency_id_2 = t2.currency_id_1
and t2.currency_id_2 = t1.currency_id_2 <--t1 added here

Adam

Celco:

joe.celko (AT) northface (DOT) edu (--CELKO--) wrote in message news:<a264e7ea.0401091735.4a5fbc7f (AT) posting (DOT) google.com>...
CREATE TABLE Rates
(
currency_code_1 INT NOT NULL,
currency_code_2 INT NOT NULL,
rate FLOAT,
flag BOOLEAN,
comment VARCHAR (30),
PRIMARY KEY (currency_code_1, currency_code_2)
)

Sample data BEFORE update:

currency_id_1 currency_id_2 rate flag comment
------------- ------------- ---- ---- --------
1 2 2 0 entry #1
2 3 2 0 entry #2
1 3 NULL 1 entry #3

Update statement should find the entry #3 and calculate the rate for
this entry using entries #1 and #2, so AFTER the update the data
should be:

currency_id_1 currency_id_2 rate flag comment
------------- ------------- ---- ---- --------
1 2 2 0 entry #1
2 3 2 0 entry #2
1 3 4 1 entry #3

....

Yes, I know about ISO, FLOAT, BOOLEAN and UPDATE-FROM. My question was
specifically about Sybase, where we have BOOLEAN and UPDATE-FROM.

I hope, that sample data clarify my question. Actually it's about how
to derive exchange rate for two currencies using two other exchange
rates involving intermediate currency.

Thank you,
Alex

Adam:

Adam H <adam_NO_SPAMM_horan (AT) yahoo (DOT) com> wrote

Your WHERE clause won't do what I want. Actually the following
statement with fully prefixed columns will do what I want:

update Rates t0
set t0.rate = t1.rate * t2.rate from Rates t1, Rates t2
where t0.currency_id_1 = t1.currency_id_1
and t1.currency_id_2 = t2.currency_id_1
and t2.currency_id_2 = t0.currency_id_2
and t0.flag = 1

BUT - Sybase doesn't allow me to use alias t0 after 'Update Rates'.

Thank you,
Alex

joe.celko (AT) northface (DOT) edu (--CELKO--) wrote in message news:<a264e7ea.0401091735.4a5fbc7f (AT) posting (DOT) google.com>...
Sorry about BOOLEAN - of course I mean BIT type.
Alex

Alex <yakovlev (AT) hotmail (DOT) com> wrote:

The table you are updating should also appear in the from list,
so your query should be:

update Rates
set rate = t1.rate * t2.rate
from Rates t0, Rates t1, Rates t2
where t0.currency_id_1 = t1.currency_id_1
and t1.currency_id_2 = t2.currency_id_1
and t2.currency_id_2 = t0.currency_id_2
and t0.flag = 1

The first table in the from list is the one that matches the table
after the 'update' keyword. So the order of the tables in the from
clause is very important.

Luc.