Informix 9: Update with FROM
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@iiug.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@fh-stralsund.de>wrote:
> 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
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>