Problem with Update Query in db2 9.5

[kelleram]

UPDATE SAGE2ADM.TICKET W SET W.TICKETSTATUS = S.STATUS FROM SAGE2ADM.STAGE_ EESMPROBLEM_NOR TH S
WHERE S.SOURCETABLENA ME=W.SOURCETABL ENAME AND S.PROBLEMNUMBER =W.PROBLEMNUMBE R AND S.STATUS<>W.TIC KETSTATUS);

This works fine in SQL Server, but not db2 9.5. I modified the SQL to be the following for db2 but then i get a full select error:
UPDATE TICKET T
SET TICKETSTATUS = (SELECT S.STATUS
FROM SAGE2ADM.STAGE_ EESMPROBLEM_NOR TH S INNER JOIN SAGE2ADM.TICKET P
ON S.SOURCETABLENA ME=P.SOURCETABL ENAME AND S.PROBLEMNUMBER =P.PROBLEMNUMBE R
WHERE S.STATUS<>P.TIC KETSTATUS);

It should update 2 records in the ticket table but it also fills up the transaction log and it shouldn't.

[nsarda2]

HI

Please paste the error message.

If the problem is resolved then paste the solutions.

Thanks

[kelleram]

Error message is as follows.

SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES
INTO statement is more than one row. SQLSTATE=21000


I'm aware that it's more than one row. It can be so how do I get around this.

[nsarda2]

Hi

The issue happening here is : inner select query is returing multiple rows at a time but update command can only update a single row at a time.

I did not find any query/command to do in simple SQLs. But following link
may be helpful for you.

http://www.dbforums.com/db2/909019-how-do-you-populate-new-column-other-tables.html

[kelleram]

Thanks for the link to that stored procedure. I've created the following stored procedure and now I'm getting a syntax error although I'm not sure why. Here is the sp:
CREATE PROCEDURE UPDATE_TICKETST ATUS_EESMNORTH
LANGUAGE SQL
BEGIN ATOMIC
FOR P AS SELECT SOURCETABLENAME , TICKETNUMBER FROM SAGE2ADM.TICKET DO
UPDATE SAGE2ADM.TICKET
SET TICKETSTATUS = (SELECT STATUS FROM
SAGE2ADM.STAGE_ EESMPROBLEM_NOR TH S WHERE S.SOURCETABLENA ME=P.SOURCETABL ENAME AND S.PROBLEMNUMBER =P.PROBLEMNUMBE R
AND S.STATUS<>P.TIC KETSTATUS)
WHERE TICKETNUMBER=P. TICKETNUMBER AND SOURCETABLENAME =P.SOURCETABLEN AME;
END FOR;
END;

For this, I get the lovely db2 generic error of: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table > ".

[Jalaj]

The update clause is missing the Where condition for the table TICKET. Pls note that in this case, the entire table will be updated!

[kelleram]

The where clause is highlighted below:

UPDATE SAGE2ADM.TICKET
SET TICKETSTATUS = (SELECT STATUS FROM
SAGE2ADM.STAGE_ EESMPROBLEM_NOR TH S WHERE S.SOURCETABLENA ME=P.SOURCETABL ENAME AND S.PROBLEMNUMBER =P.PROBLEMNUMBE R
AND S.STATUS<>P.TIC KETSTATUS)
WHERE TICKETNUMBER=P. TICKETNUMBER AND SOURCETABLENAME =P.SOURCETABLEN AME;

[Jalaj]

Your Query doesnt look ok. I dont think it would run without error.
Pls try the update as shown below:

UPDATE SAGE2ADM.TICKET P
SET TICKETSTATUS = (SELECT STATUS FROM
SAGE2ADM.STAGE_ EESMPROBLEM_NOR TH S WHERE S.SOURCETABLENA ME=P.SOURCETABL ENAME AND S.PROBLEMNUMBER =P.PROBLEMNUMBE R
AND S.STATUS<>P.TIC KETSTATUS)
Where Exists (Select 1 From SAGE2ADM.TICKET T, SAGE2ADM.STAGE_ EESMPROBLEM_NOR TH SEN where
T.SOURCETABLENA ME = SEN.SOURCETABLE NAME AND T.PROBLEMNUMBER = SEN.PROBLEMNUMB ER
AND .....)