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

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