Update one table using data from another table.

[tasty_cakes]

Trying to update a table using data from another table, and it doesn't like my syntax.

update tableA
set fieldA = 'Customer'
from tableA, tableB
where custnoA = custnoB
and fieldB = 'Customer'
and fieldA = ''

"Keyword FROM not expected. Valid tokens: SKIP WITH WHERE."

The error is that FROM is not a valid token, but I can not find an example where this is not valid SQL syntax.

What am I missing?

Basically what I am trying to do is if fieldB in tableB says "Customer", I want it to say "Customer" in fieldA in tableA if it is blank.

Many thanks!

[tomholden]

Re: Update one table using data from another table.

this should get you close...

update tableA a set a.fieldA = 'Customer'
Where a.fieldA = '' and 
exists (select 1 from tableB b where a.custnoA = b.custnoB and b.fieldB = 'Customer')

[tomliotta]

Re: Update one table using data from another table.

...but I can not find an example where this is not valid SQL syntax.

The syntax diagram of the UPDATE statement is where you would see that it's not valid syntax. There is no FROM clause shown for an UPDATE statement, and therefore none allowed.

However, it is allowed to use a sub-select that will have a FROM clause for a search-condition.

What is it that you're trying to do?

It looks like you want to change fieldA to contain 'Customer' when it's blank/empty and tableB has (1) a matching row for custnoA and (2) also has fieldB = 'Customer'. I.e., you want to duplicate a value from tableB into tableA. Under many circumstances that could be a minor violation of relational principles, but you probably have a reason to do it.

It looks like tomholden's example does what you need.

[kitvb1]

Re: Update one table using data from another table.

You could also do this:

update tableA 
set fieldA = (select fieldB from tableB
              where custnoA = custnoB
                and fieldB = 'customer')
where fieldA = ' '

If you have 2 fields from the same file that you want to update:

update tableA 
set fieldA, field2A = (select fieldB, field2B from tableB
              where custnoA = custnoB
                and fieldB = 'customer')
where fieldA = ' '

[dougkorinke]

Re: Update one table using data from another table.

You could also use the MERGE command which should be available 7.1 onwards:

http://www-01.ibm.com/support/knowle...rbafymerge.htm

Unfortunately I'm working on a way to do the same thing, we have a machine that measures dimensions where I need to bring them back into the 400 for products for a bulk update.

[dougkorinke]

Re: Update one table using data from another table.

Kit, I tried your process with the following code:

UPDATE MYLIB.MYPF A
SET (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5) = (SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5 FROM MODLIB.MODPF B WHERE A.ITEM=B.ITEM)

The target physical file fields do not allow nulls. However I am getting error SQL0407 saying that a null value was attempted in the update, however I do not have any null values in the subselect table B.

The update is failing on the first record, not really sure what the cause could be.

Any thoughts or call outs with the SQL I posted? The full error is below:

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable Field. Cause . . . . . : One of the following has occurred: -- Column FIELD1 is a target column in an UPDATE or INSERT statement for table MYPF in MYLIB. Either a null value was specified to be inserted or updated into this column or a value for the column was not specified in an INSERT statement and the column does not allow null values. The null value was specified in the relative entry number 1 in the VALUES list, select list, or SET clause. -- Column FIELD1 is specified in an ALTER statement for table MYPF in MYLIB. The attribute of column FIELD1 can not be changed to NOT NULL because a null value exists in relative entry number 1 of the column. -- Variable FIELD1 is a target variable in an SQL procedure, function, or trigger. A null value was specified to be set into this variable using a SET or VALUES statement, but the variable does not allow null values. The null value was specified in relative entry number 1 in the SET or VALUES INTO clause. The null value was specified as either NULL, a host variable with an associated indicator variable that contains a negative value, a column containing a null value, or an expression that evaluated to NULL. If it is a host variable or column then the name is *N. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the NOT NULL attribute of the column specified on the CREATE or ALTER of the column. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the partitioning key attributes of the column specified for the base table on the CREATE TABLE or ALTER TABLE statement. Recovery . . . : If this is an ALTER TABLE statement, change the existing null values in the column to a non-null value. Otherwise, change the value so the result is not null. If a host variable is specified, change the value in the related indicator variable to be greater than or equal to zero. Try the request again.

[kitvb1]

Re: Update one table using data from another table.

Hi Doug

You can use the COALESCE function or a CASE expression to cater for this. But if you say there are no NULL values in FIELD1 in MODLIB.MODPF, then I would suggest that you look further at what is causing that error.

[dougkorinke]

Re: Update one table using data from another table.

Hi Kit, I tried to use COALESCE but ran into issues on that front a few days ago. I know I definitely did some overkill on this but I just created a stored procedure with a cursor and a loop that updates the lines one at a time and then exits on the last record.

The proc gives me some flexibility as I parametrized the target and source physical files so it can be changed in the future if need be.

[jamief]

Re: Update one table using data from another table.

Doug == lets see what you code looks like today and exactly what issues are you still having,

Thanks
Jamie

[tomliotta]

Re: Update one table using data from another table.

...however I do not have any null values in the subselect table B.

Yes, you do.

Most likely, that is because (1) you're doing an UPDATE without a WHERE clause, and (2) your sub-select has a WHERE clause.

That is, you are updating every row. But you're only selecting some rows and there are rows that aren't matched in the sub-select. When there is no matching row, the values returned from the sub-select will be nulls.

Add a WHERE clause to your UPDATE. It will probably use EXISTS() so that it only tries to update when a matching row exists. The sub-select for EXISTS() will essentially be the same as you're using to SELECT values.