UPDATE using multiple tables

[khsaunderson]

I am trying to update various rows in a specific column of a table in DB2. I am, however, used to MS SQL, so am not sure how I would do this. The SELECT is..

SELECT *
FROM Tbl1
INNER JOIN Tbl2 ON Tbl2.LICCODE = Tbl1.SALLICN
INNER JOIN Tbl3 ON Tbl3.LCULICN = Tbl2.LICCODE
INNER JOIN Tbl4 ON Tbl4.CURCODE = Tbl3.LCUCURR
AND RIGHT(CURDATE,4) = RIGHT(Tbl1.SALPERIOD,4)
WHERE Tbl1.SALEXCH <> Tbl4.CURDETAIL

Normally in MS SQL I would just use the FROM bit of the statement under the UPDATE/SET bit so I could filter out to the correct fields, but DB2 doesn't accept the FROM clause.

UPDATE Tbl1
SET SALEXCH = CURDETAIL
FROM Tbl1
INNER JOIN Tbl2 ON Tbl2.LICCODE = Tbl1.SALLICN
INNER JOIN Tbl3 ON Tbl3.LCULICN = Tbl2.LICCODE
INNER JOIN Tbl4 ON Tbl4.CURCODE = Tbl3.LCUCURR
AND RIGHT(CURDATE,4) = RIGHT(Tbl1.SALPERIOD,4)
WHERE Tbl1.SALEXCH <> Tbl4.CURDETAIL

How else could I do this?

 

[MarcLodge]

Hi,
I think you need to sort out exactly what it is that you are trying to do in your SQL. Your first SQL is fine as it is only selecting data, but your 2nd statement appears to be trying to update one column with another from the same table, dependant on various joins. Those joins include a WHERE clause, but the actual update doesn't which looks rather odd to me.

Normally in DB2 SQL we would code:

UPDATE emp_act_copy
SET     actno    = 
   (SELECT MAX(salary) / 10 
    FROM   staff) 
WHERE   empno    = '200000'

Have a look at Graeme Birchall's excellent cookbook here . Page 66 will show you a number of different formats of the update statement.

Hope this helps.

Marc

 

[khsaunderson]

Ah, I see. In MS SQL you can use this syntax. Thanks for the link to the book though - my solution is to be creating a view with the information I need and working off that rather than having to have multiple joins.

Thanks

 

[khsaunderson]

Ok, so I've set a view and I still can't get it working

In basic terms I'm trying to do this:

TableA
ColA1
ColA2

TableB
ColB1
ColB2

If ColA1 = ColB1, I want ColA2 to be replaced with ColB2.

Taking an example from the DB2 cookbook, I tried
UPDATE
(SELECT TableA.*, TableB.ColB2 AS newex
FROM TableA
INNER JOIN TableB ON TableA.ColA1= TableB.ColB1
) AS change
SET ColA2 = ColB2

When I run it I get an error:
[SQL0104] Token ( was not valid. Valid tokens: <IDENTIFIER>

Any help would be appreciated.

 

[MarcLodge]

Try:

Update TableA
Set ColA2 =
(Select ColB2
from TableB
Where ColB1 = ColA1)

Let us know how you get on.

Marc

 

[khsaunderson]

Worked like a charm!

Thank you so much - making the move from T-SQL to DB2 is proving confusing!

Cheers

 

[MarcLodge]

Good luck with it, and you know you can always post your questions here.

Marc