oracle - Using "WITH" and "UPDATE" statements in the same SQL query -


i have table need update data excel spreadsheet. thinking of query along these lines:

with temp as( (select 'abcd' oldvalue, 'defg' newvalue dual) union (select .....) --about 300 lines of this, copied excel , formatted select statement ) update mytable    set name = (select newvalue temp mytable.name = temp.oldvalue)

but oracle doesn't seem having "with" , "update" statement in same query. error saying "missing select keyword". have found out can put temp table definition within select statement, i.e. 

 set name = (select newvalue (          (select 'abcd' oldvalue, 'defg'  newvalue dual) union          (select .....)           ) temp mytable.name = temp.oldvalue)

but horribly, horribly messy code define table right in middle of query. cringe thinking it. there has better way this. should set global temporary table? or missing simple syntax make work original way?

you can use clause in update; have in right place:

update mytable    set name = (with temp as((select 'abcd' oldvalue, 'defg' newvalue dual) union                             (select .....) --about 300 lines of this, copied excel , formatted select statement                            )                select newvalue                  temp                 mytable.name = temp.oldvalue);

however, you're wanting update rows exist in temp subquery, need additional clause:

update mytable    set name = (with temp as((select 'abcd' oldvalue, 'defg' newvalue dual) union                             (select .....) --about 300 lines of this, copied excel , formatted select statement                            )                select newvalue                  temp                 mytable.name = temp.oldvalue)  exists (with temp as((select 'abcd' oldvalue, 'defg' newvalue dual) union                             (select .....) --about 300 lines of this, copied excel , formatted select statement                            )                select null                  temp                 mytable.name = temp.oldvalue);

alternatively, use merge statement:

merge mytable tgt   using (with temp as((select 'abcd' oldvalue, 'defg' newvalue dual) union                       (select .....) --about 300 lines of this, copied excel , formatted select statement                      )          select mytable.rowid r_id,                 temp.newvalue            temp          inner  join mytable on mytable.name = temp.oldvalue) src     on (tgt.rowid = src.r_id) when matched update set tgt.name = src.newvalue;

n.b. have join actual table in source query of merge statement because you're trying update column that's being joined on, can't in merge statement - hence i've switched merge join join on mytable.rowid.

you'd have test both statements see 1 performant on yor data.


Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more