INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

UPDATE using multiple tables

UPDATE using multiple tables

(OP)
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?   

RE: UPDATE using multiple tables

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:

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     

RE: UPDATE using multiple tables

(OP)
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

RE: UPDATE using multiple tables

(OP)
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.

RE: UPDATE using multiple tables

Try:

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

Let us know how you get on.

Marc

RE: UPDATE using multiple tables

(OP)
Worked like a charm!

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

Cheers
 

RE: UPDATE using multiple tables

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

Marc

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close