Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: using sql to update a table

Re: using sql to update a table

From: anacedent <anacedent_at_hotmail.com>
Date: Tue, 06 Jul 2004 16:49:35 -0700
Message-ID: <eUGGc.10805$nc.3473@fed1read03>


Julie Paten wrote:
> **** 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%'

Never, ever, never use either "=" or "!=" with NULL

and a.mat_sfty_data_sht_no is not NULL
or a.mat_sfty_data_sht_no is NULL

> and b.seg_no = '11'
Received on Tue Jul 06 2004 - 18:49:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US