Problem with Update Query in db2 9.5

UPDATE SAGE2ADM.TICKET W SET W.TICKETSTATUS = S.STATUS FROM SAGE2ADM.STAGE_EESMPROBLEM_NORTH S
WHERE S.SOURCETABLENAME=W.SOURCETABLENAME AND S.PROBLEMNUMBER=W.PROBLEMNUMBER AND S.STATUS<>W.TICKETSTATUS);

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_NORTH S INNER JOIN SAGE2ADM.TICKET P
ON S.SOURCETABLENAME=P.SOURCETABLENAME AND S.PROBLEMNUMBER=P.PROBLEMNUMBER
WHERE S.STATUS<>P.TICKETSTATUS);

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

re: Problem with Update Query in db2 9.5

HI

Please paste the error message.

If the problem is resolved then paste the solutions.

Thanks

re: Problem with Update Query in db2 9.5

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.

re: Problem with Update Query in db2 9.5

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-h...er-tables.html

re: Problem with Update Query in db2 9.5

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_TICKETSTATUS_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_NORTH S WHERE S.SOURCETABLENAME=P.SOURCETABLENAME AND S.PROBLEMNUMBER=P.PROBLEMNUMBER
AND S.STATUS<>P.TICKETSTATUS)
WHERE TICKETNUMBER=P.TICKETNUMBER AND SOURCETABLENAME=P.SOURCETABLENAME;
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> ".

re: Problem with Update Query in db2 9.5

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

re: Problem with Update Query in db2 9.5

The where clause is highlighted below:

UPDATE SAGE2ADM.TICKET
SET TICKETSTATUS = (SELECT STATUS FROM
SAGE2ADM.STAGE_EESMPROBLEM_NORTH S WHERE S.SOURCETABLENAME=P.SOURCETABLENAME AND S.PROBLEMNUMBER=P.PROBLEMNUMBER
AND S.STATUS<>P.TICKETSTATUS)
WHERE TICKETNUMBER=P.TICKETNUMBER AND SOURCETABLENAME=P.SOURCETABLENAME;

re: Problem with Update Query in db2 9.5

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_NORTH S WHERE S.SOURCETABLENAME=P.SOURCETABLENAME AND S.PROBLEMNUMBER=P.PROBLEMNUMBER
AND S.STATUS<>P.TICKETSTATUS)
Where Exists (Select 1 From SAGE2ADM.TICKET T, SAGE2ADM.STAGE_EESMPROBLEM_NORTH SEN where
T.SOURCETABLENAME = SEN.SOURCETABLENAME AND T.PROBLEMNUMBER = SEN.PROBLEMNUMBER
AND .....)