Elke,
A simple example of the power of including joins in an Update
Statement:
Update Tbl1 set tbl1.field1=tbl2.field2*.10,
from Tbl1 join tbl2 on tbl1.PKfield=tbl2.tbl1FK
This will update all the joined rows in tbl1 to 10% of the values in
tbl2.
I don't see how that is possible using any of the statements you
mentioned below. As I mentioned in my earlier post, Access handles the
same thing slightly differently.
MySQL allows the same thing (from their documentation):
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The example shows an inner join using the comma operator,
but multiple-table UPDATE statements can use any type of
join allowed in SELECT statements, such as LEFT JOIN.
Are you saying that MaxDB can not update on a join?
Thank You
Sim
________________________________________________________________________________
[EMAIL PROTECTED] wrote:
>
> The problem with what you have below is the sub-select can only return a
> single value, my fetch returns more than 1. I believe the t-sql update-
> from
> can handle it but I am not certain as I have only used it in one-2-one
> conditions.
>
> Oh well, grubby vb app to the rescue.
>
> -----Original Message-----
> From: John Singleton [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 11, 2004 4:26 PM
> To: Kevin Wilson
> Cc: [EMAIL PROTECTED]
> Subject: Re: UPDATE FROM STATEMENT NOT SUPPORTED
>
>
> Kevin,
>
> After looking at what that statment does in MS SQL Server, I think this
> is the syntax you are looking for:
>
>
> UPDATE <TABLE> SET <column_name> = <subquery>| (<column_name>,...) =
> <subquery> WHERE <CONDITIONS>
>
> Hope that helps. (That update from statement is wild...)
>
>
> Cheers,
>
> JLS
UPDATE ... FROM is an extension, no SQL-standard.
I can't imagine what MS SQL Server can do, if the FROM ... will return more than one
row (and there is no constant like your 'X' given in the set-clause).
You wrote that it is a problem because the from will return more than one row, meaning
that SET columnname = (SELECT..) will not work.
I do not believe that the select should be used in the SET-clause, but in the WHERE
clause to reproduce the effect you know from MSSQL:
SET columnname = 'X' WHERE ... (SELECT ...)
With that you have several opportunities:
WHERE col <operator> ANY|ALL (SELECT ...)
WHERE EXISTS (SELECT ...)
Even correlated subquery are possible with MaxDB:
UPDATE T SET A = 'x' WHERE B = ANY (SELECT A1 FROM T1)
UPDATE T SET A = 'x' WHERE EXISTS
(SELECT * FROM T1 WHERE t1.A1=t.B and t1.x > 4711)
--> Please check what you want to do, what MSSQL does and then change to a
standard-like UPDATE-statement.
Elke
SAP Labs Berlin
>
> [EMAIL PROTECTED] wrote:
>
> >Unable to do mass update from a table using:
> >
> >UPDATE <TBL> SET <COL> = 'X' FROM <TBL A>, <TBL B> WHERE <CONDITION(S)>
> >
> >Get "Invalid end of SQL statement." on the FROM keyword. This is a real
> >handy item to have (use it in MS SQL Server). It may be a non-standard
> T-SQL
> >extension but I am not sure. Nonetheless, I would think that adding this
> to
> >your support sql would make many very happy.
> >
> >Cheers
> >
> >
> >
>
> --
> 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]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
