Re: translate this Sql Server Query into DB2 Sql for AS/400 ?

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Here's a little from MS' documentation. Your example is like the JOIN 
version here and needs to be changed to the subquery version shown first.


The following query doubles the price of all books published by New 
Moon Books. The query updates the titles table; its subquery 
references the publishers table.

UPDATE titles
SET price = price * 2
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE pub_name = 'New Moon Books')

Here's an equivalent UPDATE statement using a join:

UPDATE titles
SET price = price * 2
FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
   AND pub_name = 'New Moon Books'


So maybe something like

UPDATE Employees AS E
   SET lvl =M.LVL+1
     WHERE E.empid in
     (select i.empid from inserted AS I
     LEFT OUTER JOIN Employees AS M
     ON E.mgrid = M.empid
     where I.empid = E.empid)


At 07:43 AM 9/19/2005, you wrote:

> No, M.LVL is a numeric field and cannot be null, if employee does not have a
> manager, his LVL will be 0.
>
> Best Regards,
>
> Luqman
>
>
> "Vernon Hamberg" <vhamberg@xxxxxxxxxxx> wrote in
> message news:6.2.1.2.2.20050918065125.030ef3e0@xxxxxxxxxxxxxxxxxxx
> > I'm not sure this will do what you want in SQL Server or anywhere. The
> > problem is the LEFT OUTER JOIN - M.LVL will be NULL when the employee does
> > not have a manager in the employee file. Depending on the database
> > (maybe), the result of adding something to a NULL column is NULL - is that
> > what you want?
> >
> > I have a simpler example of this from Microsoft's MSDN site, but I'll wait
> > until you reply to the above question.
> >
> > Vern
> >
> > At 03:27 PM 9/17/2005, you wrote:
> >
> >>Can anyone translate this Sql Server Query into DB2 Sql for AS/400 ?
> >>
> >>UPDATE E
> >>   SET lvl =M.LVL+1
> >>     FROM Employees AS E JOIN inserted AS I
> >>     ON I.empid = E.empid
> >>     LEFT OUTER JOIN Employees AS M
> >>     ON E.mgrid = M.empid
> >>
> >>Best Regards,
> >>
> >>Luqman