Hi Guys,

sorry for my english but i'll try to explain my problem.

I want to update an table called user. But i have to connect in the
WHERE-condition two tables over an index. Therefore my statement looks like:

UPDATE user
SET user.onl_login = (SELECT email.email FROM email WHERE
email.artkz='RZ' AND email.identnr = 4517)
FROM user, identroll
WHERE user.identnr = 4517 AND user.identroll = identroll.identnr AND
identroll.rolle ='S';

When i want to update the value, an error occurs. The FROM-tag seems to
be unknown in the UPDATE-statement. But when i look at the official
documentation of informix, then the usage of FROM seems to be okay.

Could somebody solve my problem?

Thx in advance.

Michael

Hello guys,

i found a solution. I have to use the IN-Operator and therefore the
SQL-Statement looks like:

UPDATE user
SET user.onl_login = (
SELECT email.email FROM email WHERE email.artkz='RZ' AND email.identnr =
user.identnr
)
WHERE user.identnr IN (
SELECT identnr
FROM identroll
WHERE identroll.rolle ='S' AND identroll.identnr=user.identnr
);

Now, everything is fine. Thx a lot.

Greets.

Michael

UPDATE user
SET user.onl_login = (SELECT email.email FROM email WHERE
email.artkz='RZ' AND email.identnr = 4517)
WHERE rowid in (
SELECT user.rowid
FROM user as user2, identroll
WHERE user2.identnr = 4517
AND user2.identroll = identroll.identnr
AND identroll.rolle ='S'
);
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Mon, Jun 7, 2010 at 9:14 AM, M. Weber <michael.weber (AT) fh-stralsund (DOT) de>wrote: