Help With an Update 2

[TitleistDBA]

I keep getting an error on this update. For some reason it doesn't like the FROM clause. Can anyone help?

UPDATE MITBAL
SET MBWHSL = 'SPECIAL'
FROM MVXCJDTDVL.MITMAS, MVXCJDTDVL.MITBAL
WHERE MVXCJDTDVL.MITMAS.MMITTY in ( 'wM1','WM2' ,'3LY','3L1')
AND MVXCJDTDVL.MITMAS.MMCONO = MVXCJDTDVL.MITBAL.MBCONO
AND MVXCJDTDVL.MITMAS.MMITNO = MVXCJDTDVL.MITBAL.MBITNO;

http://www.acushnetcompany.com

 

[MarcLodge]

You are specifying the table you want updated in the UPDATE MITBAL statement, setting all values of the MBWHSL column to 'SPECIAL'. The FROM part is kind of illogical (you've already told DB2 what table it's from - MITBAL), but I expect that you are wanting to say:

UPDATE MITBAL where the rows can be found in this subselect (SELECT BLAH FROM MITMAS etc.).

Hope I've managed to explain this clearly, if not, get back to me.

Marc

 

[LarryKDB2]

Hi "TitleistDBA",

I think you want to do this:

UPDATE MVXCJDTDVL.MITBAL AS B
SET MBWHSL = 'SPECIAL'
WHERE EXISTS
( SELECT * FROM MVXCJDTDVL.MITMAS AS M
WHERE M.MMITTY IN ( 'wM1','WM2' ,'3LY','3L1') --careful l.c
AND M.MMCONO = B.MBCONO
AND M.MMITNO = B.MBITNO
);
I noted the lower case 'w'could give you a problem!

Best performance will likely be with an index on MITMAS (MMCON, MMITNO, MMITTY).

If you are in DB2 UDB for z/OS, the reference manuals are online ormay be downloaded as .PDF files. Go to

http://www.ibm.com/db2

choose the version you are using, on the left panel choose LIBRARY. choose the correct version and scroll down to the many manuals available.

If you are in DB2 UDB for Win-Unix in the Command Center go to HELP -> Information Center to find all of the manuals [even z/OS!]

If you check the SQL reference if there is anything that you don't understand in the UPDATE STATEMENT description, look up [or click on links for] the terms in the index.

If you still have problems, post a question.

If you have problems with slow running queries in your shop check my web site

http://www dot

DBIndexDesign dot com.

Larry Kintisch

IBM Contractor Instructor for DB2, SQl, QMF, Data modeling; [DB2 for z/OS]

 

[stevexff]

TitleistDBA

A couple of people took the time to help you out here, for free. Unless you were hit by a truck in the meantime, it's customary to post a 'thank you'.

(Obviously, if you were hit by a truck, accept my apologies and get well soon...)

 

[PruSQLer]

And a dozen Pro V1 golf balls would be an excellent way to say thank you. If no one who helped plays golf, I'd be glad to accept for them.

 

[TitleistDBA]

Larry,
Thanks for your help. That is just what I wanted. I would offer up some proV1's But being in the IS dept we don't have great access to the goodies.

http://www.acushnetcompany.com

 

[TitleistDBA]

btw Steve I was hit by a truck and it's name is MoveX by Intentia!

http://www.acushnetcompany.com

 

[stevexff]

It's the same the whole world over - marketing get the goodies, and IT gets the blame...