Re: SQL: how to update a column in tableA from a column in tableB ?

Basically, you need to duplicate the sub-query in the WHERE, something like
so:

update jstone/docs00
set keyword7 =
(select seal1 from load
where ashpnbr = substr(keyword1,1,10)
and shipdate =
'20'||substr(keyword2,7,2)||substr(keyword2,1,2)||
substr(keyword2,4,2) )
where exists (select * from load
where ashpnbr = substr(keyword1,1,10)
​ ​
and shipdate =

​ ​
'20'||substr(keyword2,7,2)||substr(keyword2,1,2)||
​ ​

​ ​
​
substr(keyword2,4,2)
and substr(keyword7,1,10) <> seal1
and substr(keyword7,1,6) = substr(seal1,1,6)
)
and substr(keyword2,7,2) = '14' -- only fix 2014 records
and docid = 'AS400DOC-000000016734036'


If you're on 7.1, (6.1?) you might look at MERGE

merge into jstone/docs00
using ( select seal1, ashpnr, shipdate from load
) as T
on T.ashpnbr = substr(keyword1,1,10)
and T.shipdate =
'20'||substr(keyword2,7,2)||substr(keyword2,1,2)||
substr(keyword2,4,2) )
when matched
and substr(keyword7,1,10) <> T.seal1
and substr(keyword7,1,6) = substr(
​T.​
seal1,1,6)
and substr(keyword2,7,2) = '14' -- only fix 2014 records
and docid = 'AS400DOC-000000016734036'
​ update ​set keyword7 = T.seal1

​Charles​


On Fri, Dec 5, 2014 at 12:30 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

I want to update keywork7 column from another joined table. I want to
update ONLY after comparing one column from one table to one column from
the second table (as shown below at Error1).

How can I accomplish this in SQL?

The code below throws an error: Column SEAL1 not in specified tables.


Thanks!







update jstone/docs00
set keyword7 =
(select seal1 from load
where ashpnbr = substr(keyword1,1,10)
and shipdate =
'20'||substr(keyword2,7,2)||substr(keyword2,1,2)||
substr(keyword2,4,2) )
where
substr(keyword7,1,10) <> seal1
<------Error1: how to compare one column from
each table
and substr(keyword7,1,6) = substr(seal1,1,6)
and substr(keyword2,7,2) = '14' -- only fix 2014 records
and docid = 'AS400DOC-000000016734036'

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.