Hi



Thanks for the help!!! And, yes, “is” is a reserved word, so with that
small modification it worked fine.



Bill





_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Tuesday, February 12, 2008 11:22 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] SQL question - update with a join



Unless things have changed since Firebird 1.5.4, IS is a reserved word,
hence make the alias ISU or something.

And one further comment, Bill. With a few exceptions, things in the WHERE
clause that refers to a table on the right side of a left join, in reality
changes the join to an INNER JOIN only with more restrictions on what the
optimizer can do (often a bad idea) and makes your sql more difficult to
read (that's often a bad idea, too). As mentioned, there are a few
exceptions, but generally, use inner JOINs (that's the default if you just
specify JOIN) when possible, and LEFT (RIGHT, OUTER) JOINs when required.
Though, as Helens solution shows, you do not want a join at all in this
case.

HTH,
Set

-----Original Message-----
From: Helen Borrie

update INVENTORY i
set i.INV_QTYONORDER = 0
where exists
(select 1 from INVENTORY_SUPPLIER is
where is.INVSUP_INVID = i.INV_ID
and is.INVSUP_SUPPNUM = :suppnum
and i.INV_QTYONORDER <> 0)

>update INVENTORY
>set INVENTORY.INV_QTYONORDER = 0
>from INVENTORY
>left outer join INVENTORY_SUPPLIER
>on (INVENTORY.INV_ID = INVENTORY_SUPPLIER.INVSUP_INVID)
>where INVENTORY.INV_QTYONORDER <> 0
>and INVENTORY_SUPPLIER.INVSUP_SUPPNUM = 10

[Non-text portions of this message have been removed]