I am attempting to update one column in a table with the value from a
second table, using the code below:

UPDATE TABLEA
SET (PYMT_BEFORE_MOD) = (SELECT NEW_PYMT_BEFORE_MOD
FROM TABLEB
WHERE TABLEB.ACCOUNT = TABLEA.ACCOUNT)

There are 16 rows out of 600 that meet criteria that I want to
update. But the statement above updated all 600 rows with NULL.

What am I doing wrong?

J

Hi Joe,

On 27.01.11 16:59 , Joe wrote:
Several things:

1) you are missing the WHERE clause for the UPDATE statement
2) your SELECT statement most likely does not return a single value or returns
NULL

If you do not specify a where clause for the update statement, all rows are
updated.
What do you get when you run the following statement:
SELECT NEW_PYMT_BEFORE_MOD FROM TABLEB
WHERE TABLEB.ACCOUNT = TABLEA.ACCOUNT


--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

On Jan 27, 3:23*pm, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
When I run that query I get an error message "Error: SQL0206N
"TABLEA.ACCOUNT" is not valid in the context where it is used."
Probably because I am not referencing TABLEA

When I execute this statement: SELECT NEW_PYMT_BEFORE_MOD
FROM TABLEB
JOIN TABLEA ON
TABLEB.ACCOUNT = TABLEA.ACCOUNT

it returns the 16 rows of data that I expect.

So I think you are saying that I need to have an additional WHERE
clause outside of the last right parenthesis?

Hi Joe,

On 27.01.11 18:30 , Joe wrote:
update tableA
set (col) = (<new value>)
where <which rows you want to update>

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

On 1/27/2011 4:59 PM, Joe wrote:
SET (PYMT_BEFORE_MOD) = (SELECT NEW_PYMT_BEFORE_MOD
FROM TABLEB
WHERE TABLEB.ACCOUNT = TABLEA.ACCOUNT)
WHERE EXISTS(SELECT 1 FROM TABLEB
WHERE TABLEB.ACCOUNT = TABLEA.ACCOUNT)

or
MERGE INTO TABLEA USING TABLEB
ON TABLEB.ACCOUNT = TABLEA.ACCOUNT
WHEN MATCHED THEN UPDATE SET PYMT_BEFORE_MOD = NEW_PYMT_BEFORE_MOD

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau