dbaccess Error in UPDATE with query over 3 Tables
Hello,
I have 3 Tables wich are related 1:1 with the field artikel
Table1
fyar1sta Fields: artikel, artwgr
fyar2sta Fields artikel, wbzkrit
fyar3sta Fields artikel karenzzt
I must copy the value of wbzkrit to karenzzt for all Records with
wbzkrit> and artwgr 301 to 306
I made this Update statement but I get the followig error messages:
Database selected.
522: Table (fyar1sta) not selected in query.
Error in line 7Near character position 20
Database closed.
update fyar3sta
set karenzzt = ( select wbzkrit from fyar2sta
where fyar3sta.artikel=fyar2sta.artikel
)
where artikel in ( select artikel from fyar2sta )
and karenzzt>0
and fyar1sta.artwgr between 301 and 306;
Whre is my mistake and how should I make the right statement?
Thanks
Ralf
http://groups.google.de/group/comp.databases.informix/browse_thread/thread/76738cc6f121280d
Ralf Hackmann wrote:
> Hello,
> I have 3 Tables wich are related 1:1 with the field artikel
>
> Table1
> fyar1sta Fields: artikel, artwgr
> fyar2sta Fields artikel, wbzkrit
> fyar3sta Fields artikel karenzzt
>
> I must copy the value of wbzkrit to karenzzt for all Records with
> wbzkrit> and artwgr 301 to 306
>
> I made this Update statement but I get the followig error messages:
> Database selected.
> 522: Table (fyar1sta) not selected in query.
> Error in line 7> Near character position 20
> Database closed.
>
> update fyar3sta
> set karenzzt = ( select wbzkrit from fyar2sta
> where fyar3sta.artikel=fyar2sta.artikel
> )
> where artikel in ( select artikel from fyar2sta )
> and karenzzt>0
> and fyar1sta.artwgr between 301 and 306;
>
> Whre is my mistake and how should I make the right statement?
You haven't told it how to join fyar1sta to anything else.
Without giving it too much thought I think you need to make the
subselect into something along the lines of:
SELECT fyar2sta.artikel
FROM fyar1sta, fyar2sta
WHERE fyar1sta.artikel = fyar2sta.artikel AND fyar1sta.artwgr BETWEEN301 AND 306
--
Ian
Hotmail is for spammers. Real mail address is igoddard
at nildram co uk
Hi.
Error is at line "and fyar1sta.artwgr between 301 and 306", because you didn't use table at any from or update clause. I think you should do as follows:
update fyar3sta
set karenzzt = ( select wbzkrit from fyar2sta
where fyar3sta.artikel=fyar2sta.artikel
)
where artikel in ( select artikel from fyar2sta )
and karenzzt>0
and artikel in (select artikel from fyar1sta
where fyar1sta.artwgr between 301 and 306);
But beware!, this kind of queries are tricky. I recommend you comparing this query using "select * from " instead of "update" versus a well-written select with proper joins as you explained : they must be the same.
Later
Omar Muñoz
--- On Wed, 8/5/09, Ralf Hackmann <ralf.hackmann@gmail.com> wrote:
> From: Ralf Hackmann <ralf.hackmann@gmail.com>
> Subject: dbaccess Error in UPDATE with query over 3 Tables
> To: informix-list@iiug.org
> Date: Wednesday, August 5, 2009, 7:04 AM
> Hello,
> I have 3 Tables wich are related 1:1 with the field
> artikel
>
> Table1
> fyar1sta Fields: artikel, artwgr
> fyar2sta Fields artikel, wbzkrit
> fyar3sta Fields artikel karenzzt
>
> I must copy the value of wbzkrit to karenzzt for all
> Records with
> wbzkrit> and artwgr 301 to 306
>
> I made this Update statement but I get the followig error
> messages:
> Database selected.
> 522: Table (fyar1sta) not selected in query.
> Error in line 7> Near character position 20
> Database closed.
>
> update fyar3sta
> set karenzzt = ( select wbzkrit from fyar2sta
>
> where fyar3sta.artikel=fyar2sta.artikel
> )
> where artikel in ( select artikel from fyar2sta )
> and karenzzt>0
> and fyar1sta.artwgr between 301 and 306;
>
> Whre is my mistake and how should I make the right
> statement?
>
> Thanks
>
> Ralf
>
>
>
> http://groups.google.de/group/comp.databases.informix/browse_thread/thread/76738cc6f121280d
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>