| Subject | Re: UPDATE AND DELETE WITH JOIN |
|---|---|
| Author | Adam |
| Post date | 2006-07-11T23:44:02Z |
> Hello,It may be a valid sentence under SQL Server, but
> This is a valid sentence with SQL Server:
> UPDATE MAT SET MAT.MT=137
> FROM MAT INNER JOIN ALU ON MAT.ALREG = ALU.ALREG
> WHERE ALU.EDCOD='TWQ';
> With FireBird 1.5 I get Token unknow line 2 char 1 FROM
> How can I do this kind of UPDATE with FireBird 1.5?
> How can I do a similar DELETE (with JOIN) with FireBird 1.5?
>
<html>
<body>
<p>This
<p> is valid <B>html</B> in MS IE.
</html>
My understanding is that the above syntax you provided is not part of
any SQL Standard. That alone does not mean it will never be supported,
sometimes a standard takes a long time to play catch up, but it is a
pretty good indication that one should not be expecting it to work in
any other database.
If I am mistaken and this syntax does appear in the SQL standard, then
it could be put through as an enhancement request.
But the basic syntax for an update statement is something like:
UPDATE <target table> [ [ AS ] <correlation name> ]
SET <set clause list>
[ WHERE <search condition> ]
To solve what I THINK your query is going to do in MSSQL, that is to
set mt to 137 where there is a matching ALU record based on the ALREG
fields and in the matching ALU record, EDCOD = 'TWQ'.
UPDATE MAT M SET
M.MT=137
WHERE EXISTS
(SELECT * FROM ALU A WHERE M.ALREG = A.ALREG AND A.EDCOD='TWQ')
This reads a lot better than the original query.
Adam