Re: Is this a VALID query ???

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/26
Message-ID: <8e6hum$l7k$1@nnrp1.deja.com>#1/1

In article <8e4m3g$a53_at_nntpa.cb.lucent.com>,   "PCGUY" <pcguy_at_lucent.com> wrote:
> I'm using ASP to run a query against an oracle database, but it
 reports an
> invalid column name...typically I know this is usually a misspelling
 or
> something...But I wanted to verify that you can do a JOIN with other
> criteria in the WHERE clause with an UPDATE query...
>
> So is this query properly structured? Thanks in advance!
>
> SQLStmt = "update repair "
>
> SQLStmt = SQLStmt & "set repair_action_code = '" & action & "', "
>
> SQLStmt = SQLStmt & "repair_cause_code = '" & cause & "', "
>
> SQLStmt = SQLStmt & "part_number = '" & part & "', "
>
> SQLStmt = SQLStmt & "reference_designator = '" & reference & "' "
>
> SQLStmt = SQLStmt & "where product_event.event_id = " &
> session("event_id_repair") & " "
>
> SQLStmt = SQLStmt & "and product_event.assembly_serial_number = '" &
> session("serial_number_repair") & "' "
>
> SQLStmt = SQLStmt & "and product_event.event_id = repair.event_id "
>
> SQLStmt = SQLStmt & "and repair.repair_action_code = '" & old_action
 & "' "
>
> SQLStmt = SQLStmt & "and repair.repair_cause_code = '" & old_cause
 & "' "
>
> SQLStmt = SQLStmt & "and repair.part_number = '" & old_part & "' "
>
> SQLStmt = SQLStmt & "and repair.reference_designator = '" &
 old_refdes & "'
> "
>
> Set RS = Connection.Execute(SQLStmt)
>
>

You are building an update that would look like:

update repair

   set repair_action_code = ' & action & ',

       repair_cause_code = ' & cause & ',
       part_number = ' & part & ',
       reference_designator = ' & reference & '
 where product_event.event_id =  & session(event_id_repair) &
   and product_event.assembly_serial_number = ' & session

(serial_number_repair) & '

   and product_event.event_id = repair.event_id

   and repair.repair_action_code = ' & old_action & '
   and repair.repair_cause_code = ' & old_cause & '
   and repair.part_number = ' & old_part & '
   and repair.reference_designator = ' & old_refdes & '

that in itself is not valid -- there are a couple of ways to do this. The easiest I think would be this:

update repair

   set repair_action_code = ' & action & ',

       repair_cause_code = ' & cause & ',
       part_number = ' & part & ',
       reference_designator = ' & reference & '
 where event_id =
         ( select event_id
             from product_event
            where product_event.event_id =  & session(event_id_repair)
&
                       and product_event.assembly_serial_number = ' &

session(serial_number_repair) & ' )

   and repair_action_code = ' & old_action & '    and repair_cause_code = ' & old_cause & '    and part_number = ' & old_part & '
   and reference_designator = ' & old_refdes & ' /

Please also learn about using bind variables!!!! This is extremely important. Putting character string constants into an update like the above is a terrible practice. In most cases, upto 90% or more of the execution time of DML is spent *parsing and optimizing the query* when you do not use bind variables. By using bind variables in the query -- we can reduce that to almost ZERO after the first parse. We'll reuse the parsed query and plan across all sessions -- avoiding the continual parse/optimize step. Your app will scale better and your database will run faster and you won't run into issues with strings with quotes and strings being too long and so on.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Apr 26 2000 - 00:00:00 CDT