[EMAIL PROTECTED] wrote:
>
> Hallo Elke
>
> Here some more information:
>
> Table A
> ********
> CREATE TABLE "BWALLE"."RBOBJEKTPOS"
> (
> "OBJEKTPOSID" Char (8) ASCII NOT NULL,
> "OBJEKTID" Char (8) ASCII NOT NULL DEFAULT ' ',
> "POSNR" Fixed (6,0) NOT NULL DEFAULT 0,
> "POSART" Char (10) ASCII NOT NULL DEFAULT ' ',
> "NL" Char (3) ASCII NOT NULL DEFAULT ' ',
> "VERTRAGSNR" Char (10) ASCII NOT NULL DEFAULT '
> ',
> "AUFTRAGSNR" Char (10) ASCII NOT NULL DEFAULT '
> ',
> "RAUMNR" Char (10) ASCII NOT NULL DEFAULT ' ',
> "AKTIVKZ" Fixed (1,0) NOT NULL DEFAULT 0,
> "HAEUFIGKEIT" Fixed (8,2) NOT NULL DEFAULT
> 0.00,
> "POSMENGE" Fixed (10,2) NOT NULL DEFAULT
> 0.00,
> "LEISTUNGSID" Char (8) ASCII NOT NULL DEFAULT '
> ',
> "EINZELPREIS" Fixed (10,2) NOT NULL DEFAULT
> 0.00,
> "BEZUGSMONAT" Char (2) ASCII NOT NULL DEFAULT '
> ',
> "BEZUGSJAHR" Char (4) ASCII NOT NULL DEFAULT '
> ',
> "KUNDENMENGE" Fixed (10,2) NOT NULL DEFAULT
> 0.00,
> "KUNDENKOSTST" Char (20) ASCII NOT NULL DEFAULT
> ' ',
> "SONDERLEISTUNG" Varchar (254) ASCII NOT NULL
> DEFAULT ' ',
> "FAKTURIERKZ" Fixed (1,0) NOT NULL DEFAULT 0,
> "INVENTARID" Char (8) ASCII NOT NULL DEFAULT '
> ',
> "INVENTARBEZEICH" Varchar (255) ASCII NOT NULL
> DEFAULT ' ',
> "ANGELEGT" Timestamp DEFAULT TIMESTAMP,
> "GEAENDERT" Timestamp DEFAULT TIMESTAMP,
> "A_BENUTZER" Char (20) ASCII NOT NULL DEFAULT '
> ',
> "G_BENUTZER" Char (20) ASCII NOT NULL DEFAULT '
> ',
> PRIMARY KEY ("OBJEKTPOSID")
> )
>
>
> Table B
> ********
> CREATE TABLE "BWALLE"."RBINVENTAR"
> (
> "INVENTARID" Char (8) ASCII NOT NULL,
> "INVENTARBEZEICH" Varchar (255) ASCII NOT NULL
> DEFAULT ' ',
> "ANGELEGT" Timestamp DEFAULT TIMESTAMP,
> "GEAENDERT" Timestamp DEFAULT TIMESTAMP,
> "A_BENUTZER" Char (20) ASCII NOT NULL DEFAULT '
> ',
> "G_BENUTZER" Char (20) ASCII NOT NULL DEFAULT '
> ',
> PRIMARY KEY ("INVENTARID")
> )
>
>
>
> ***************************
>
> I need inventarbezeich of table b in inventarbezeich of table a if
> a.inventarid <> ' ' ???
>
> ***************************
>
> I tried:
>
> ---- Error -------------------------------
> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> General error;-7015 POS(1) More than one result row not allowed.
> update rbobjektpos set inventarbezeich = (select
> rbinventar.inventarbezeich from rbinventar,rbobjektpos where
> rbinventar.inventarid = rbobjektpos.inventarid) where
> rbobjektpos.inventarid <> ' '
>
as I tried to point out in my response to 'Jean-Michel OLTRA':
now you are preparing in the subquery a result containing ALL combination of
inventar and objectpos with corresponding inventarid. As we have to assume that
you have more than 1 entry in each of those tables, the subquery will return
MANY results. Which result should be used for update???? The error is correct
as you did not specify that you wanted to update with the value which is in the
inventar-row with the same inventarid as is in the row you want to update.
> ---- Error -------------------------------
> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> Syntax error or access violation;-5015 POS(19) Missing keyword:SET.
> update rbobjektpos,rbinventar set rbobjektpos.inventarbezeich =
> rbinventar.inventarbezeich where rbobjektpos.inventarid =
> rbinventar.inventarid
>
As reference manual tells us: only one tablename is possible in the
update-clause:
http://dev.mysql.com/doc/maxdb/en/34/ee7fba293911d3a97d00a0c9449261/frameset.htm
no further comment.
> ---- Error -------------------------------
> Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> General error;-8010 POS(126) Table name must be in from list.
> update rbobjektpos set inventarbezeich = (select
> rbinventar.inventarbezeich from rbinventar) where
> rbobjektpos.inventarid = rbinventar.inventarid
>
correct error as in the where-clause of an update/delete/most upper-select only
columns from tables can be used which are in the from-clause / specified behind
the keyword update/delete.
>
> What is the right sql-syntax to do the update ????
> Any help welcomed
>
In your first mail it looked like the correct syntax:
update a set text = (select text from b where b.textid = a.textid) where
a.textid <> ' '
==>
update rbobjektpos set inventarbezeich =
(select rbinventar.inventarbezeich
from rbinventar
where rbinventar.inventarid = rbobjektpos.inventarid)
where rbobjektpos.inventarid <> ' '
Why didn't you use this? And as I asked before: what did
" but i can't get it running " means?
Elke
SAP Labs Berlin
> Albert
>
>
> '''''
> '''''''''
> (0 0)
> +---------oOO-------(_)-------------------+
> | Tel: 0541/5841-868 |
> | Fax: 0541/5841-869 |
> | Mail: mailto:[EMAIL PROTECTED] |
> | Internet: http://www.piepenbrock.de |
> +-------------------------------oOO-------+
> |__|__|
> || ||
> ooO Ooo
>
>
>
> -----Urspr�ngliche Nachricht-----
> Von: elke.zabach [mailto:[EMAIL PROTECTED]
> Gesendet am: Freitag, 3. Dezember 2004 12:57
> An: Beermann, Albert; maxdb
> Betreff: AW: SQL syntax ?????
>
> [EMAIL PROTECTED] wrote:
>
> >
> > Hello Everbody !
> >
> > SQL for beginners or just to stupid ????
> >
> > I have to sync to tables in maxdb
> >
> > table a
> > *********
> > textid no key !!
> > text
> > ...
> > ...
> > ...
> >
> > table b
> > **********
> > textid key !!
> > text
> >
> > if textid of table a <> ' ' i need the text of table b
> >
> > i am thinking of something like this:
> >
> > update a set text = (select text from b where b.textid = a.textid)
> where
> > a.textid <> ' '
> >
> > but i can't get it running
> >
>
> Would you mind telling us what " but i can't get it running " means?
> Error, wrong results, ???
> Which version do you use?
> Is text a LONG-column?
>
> Sometimes some more details are really helpful.
>
> Elke
> SAP Labs Berlin
>
> > Any help welcomed
> > Best regards
> > Albert
> >
> >
> > --
> > MaxDB Discussion Mailing List
> > For list archives: http://lists.mysql.com/maxdb
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
