Home » SQL & PL/SQL » SQL & PL/SQL » Update based on Select Query
Update based on Select Query [message #191909] Fri, 08 September 2006 10:16 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I have the following query: -

select      tr.id, tr.trans_num, tr.trans_date
from        transactions tr, customers cu
where       tr.bookkeeping_date between TO_DATE('08.09.2006', 'dd.mm.yyyy') AND TO_DATE('08.09.2006', 'dd.mm.yyyy')
and         tr.card_num = cu.card_num
and         tr.exported is null
and         cu.card_num = '1498'


Using the records returned I want to update the field 'exported' on the 'transactions' table.

I've tried the following: -

update          transactions tr set exported = '1'
where tr.id in (select      tr.id, tr.trans_num, tr.trans_date
                from        transactions tr, customers cu
                where       tr.bookkeeping_date between TO_DATE('08.09.2006', 'dd.mm.yyyy') AND TO_DATE('08.09.2006', 'dd.mm.yyyy')
                and         tr.card_num = cu.card_num
                and         tr.exported is null
                and         cu.card_num = '1498')


But the error 'ORA-00913: too many values' is returned, but I don't understand why?

Regards

Andrew
Re: Update based on Select Query [message #191917 is a reply to message #191909] Fri, 08 September 2006 11:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>where tr.id in (select tr.id, tr.trans_num, tr.trans_date
make above look like below
where tr.id in (select tr.id
Re: Update based on Select Query [message #192032 is a reply to message #191909] Sun, 10 September 2006 00:26 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
update (select tr.exported
from transactions tr, customers cu
where tr.bookkeeping_date between TO_DATE('08.09.2006', 'dd.mm.yyyy') AND TO_DATE('08.09.2006', 'dd.mm.yyyy')
and tr.card_num = cu.card_num
and tr.exported is null
and cu.card_num = '1498')
set exported = '1'
Re: Update based on Select Query [message #192040 is a reply to message #192032] Sun, 10 September 2006 03:56 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This syntax is a new Oracle feature? Could you post a link to the documentation about it, please?
Re: Update based on Select Query [message #192052 is a reply to message #192040] Sun, 10 September 2006 10:44 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi I am not sure where it is in the docs, but You will probably find it just as fast as I can. Search in there for update....
Searching google with this: site:oracle.com update select
- first hit is asktom, where he uses this construct on 9i.

So no, it definitely existed in 9i.

Br
Kim
Re: Update based on Select Query [message #192057 is a reply to message #192040] Sun, 10 September 2006 12:10 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It's called Inline View Update, but in typical Oracle fashion, the documentation has sparse information with examples on this concept. AskTom has some information here.
Re: Update based on Select Query [message #192137 is a reply to message #192057] Mon, 11 September 2006 03:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you; it definitely IS a new feature to me; I've never seen it before.
Re: Update based on Select Query [message #192187 is a reply to message #191909] Mon, 11 September 2006 07:14 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

update transactions tr set exported = '1'
where tr.id in (select tr.id
from transactions tr, customers cu
where tr.bookkeeping_date between TO_DATE('08.09.2006', 'dd.mm.yyyy') AND TO_DATE('08.09.2006', 'dd.mm.yyyy')
and tr.card_num = cu.card_num
and tr.exported is null
and cu.card_num = '1498')


You have to remove the tr.trans_num, tr.trans_date from subquery

thanks,

Mohan Reddy G
Re: Update based on Select Query [message #192238 is a reply to message #192187] Mon, 11 September 2006 09:08 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Well While updating through view(the method given above), you have to ensure that the table that you going select from, must have unique or primary key. otherwise eventually you will end up with an error "ORA-01779: cannot modify a column which maps to a non key-preserved table".

Thanks,
Thangam
Re: Update based on Select Query [message #192252 is a reply to message #192187] Mon, 11 September 2006 10:07 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
But that is not optimal, as it gives him two lookups in the transactions table, when only one is needed.
Re: Update based on Select Query [message #192315 is a reply to message #192252] Mon, 11 September 2006 16:30 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
I would do it like this

update transactions tr set exported = '1'
where tr.bookkeeping_date between TO_DATE('08.09.2006', 'dd.mm.yyyy') AND TO_DATE('08.09.2006', 'dd.mm.yyyy')
and tr.exported is null
and tr.card_num = '1498'
and exists
(select null
from customers cu
where tr.card_num = cu.card_num
and cu.card_num = '1498');
Re: Update based on Select Query [message #192369 is a reply to message #192315] Tue, 12 September 2006 01:33 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
That is a very good idea, if transactions are a small table, and customers are big.
- You force the optimizer to nested loop into customers....

I normally prefer to let the optimizer make the decision based on the statistics.
Previous Topic: IN List
Next Topic: Question about using Max
Goto Forum:
  


Current Time: Sat Dec 03 11:50:39 CST 2016

Total time taken to generate the page: 0.22416 seconds