Home » SQL & PL/SQL » SQL & PL/SQL » SQL query problem
SQL query problem [message #248510] Fri, 29 June 2007 04:55 Go to next message
Priyabrata
Messages: 1
Registered: June 2007
Junior Member
Hi,
If I use IN operator in a query how does it performs? I was using tables like
BANK(bank_id,bank_name,cust_id)
CUSTOMER(cust_id,cust_name)

and my query was

update BANK set bank_name='ABCD'
where bank_id in (select bank_id from CUSTOMER)

which is eventually updating all rows of BANK without giving ORA:00904 Invalid Identifier error. Mad Can any one help me why it is happenning?

Thanks,
Priyabrata
Re: SQL query problem [message #248514 is a reply to message #248510] Fri, 29 June 2007 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want it gives you an error?

bank_id is defined in BANK so it is known in subquery.
Your where clause is equivalent to "1=1".

Regards
Michel
Re: SQL query problem [message #248548 is a reply to message #248510] Fri, 29 June 2007 08:20 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And just to add to Michel's response, give an alias to every table and every column so you can tell Oracle which column from which table to use and to make it clearer to yourself.
Re: SQL query problem [message #248552 is a reply to message #248548] Fri, 29 June 2007 08:32 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good advice!

Regards
Michel
Previous Topic: max length of the date function
Next Topic: Performance of Exist and IN
Goto Forum:
  


Current Time: Tue Dec 06 04:37:11 CST 2016

Total time taken to generate the page: 0.17683 seconds