using sql to update a table

From: Julie Paten <jpaten_at_sge.com>
Date: Tue, 6 Jul 2004 16:35:10 +1000
Message-ID: <40ea481d$1@post.usenet.com>

Post for FREE via your newsreader at post.usenet.com ****

Hello,

I am using sql+ to try and update a table and am having some trouble.

Below is a select statement with the result I want to acheive. I want to get this result by using update to update my table. I have attempted to create the update sql statement, see below.

I guess my major problem is concatenating results from the same table into an update.

I'm new at sql and am wondering if this best be done using plsql?

Any suggestions would be great!

Thanks Julie

________________________select statement ______________________

select a.part_no, a.mat_sfty_data_sht_no || '_' ||b.mat_sfty_data_sht_no as RESULTS
FROM part_seg_jp a,
  part_seg_jp b
where a.part_no = b.part_no
  and soundex(a.mat_sfty_data_sht_no) = soundex('K%')   and a.seg_no = 16
  and soundex(b.mat_sfty_data_sht_no) != soundex('K%')   and b.seg_no = 11

______________________This is what the sql result is____________________

Part_NO Results

28            K2_SXW
47            K2_SXW
71            K2_SXW



_____________________The update statement________________________

update part_seg_jp b

set b.mat_sfty_data_sht_no = mat_sfty_data_sht_no || '_' ||
b.mat_sfty_data_sht_no
where mat_sfty_data_sht_no in (select a.mat_sfty_data_sht_no
          FROM part_seg_jp a
          WHERE soundex(a.mat_sfty_data_sht_no) = soundex('K%')
          and a.seg_no = '16'
       and a.mat_sfty_data_sht_no != 'NULL')

and b.mat_sfty_data_sht_no not like '%K%' and b.seg_no = '11'

______________________The update result_____________________

0 rows updated.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Tue Jul 06 2004 - 01:35:10 CDT