Home » SQL & PL/SQL » SQL & PL/SQL » Update/Select
Update/Select [message #345448] Wed, 03 September 2008 11:51 Go to next message
RicM
Messages: 2
Registered: September 2008
Junior Member
HI all,

I need help with a update/select statement

for example, I have this two tables:

t1
id c1
----------- --
1 A
2 B
3 C

t2
id c1
----------- --
1 X

Now I want to construct a statement SQL to update c1 from t1 where they have the same id.

I'm trying the follwing one:

update t1 set t1.c1 = (select t2.c1 from t2
where t1.id = t2.id);

But this is not working, any ideas?

Thanks in Advance.

Ric
Re: Update/Select [message #345450 is a reply to message #345448] Wed, 03 September 2008 12:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No, no idea.

Read the Forum Guide and post accordingly, then maybe we see what is going on and get some ideas.
Re: Update/Select [message #345454 is a reply to message #345448] Wed, 03 September 2008 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But this is not working,

What do you mean?
Maybe you should add a where clause?

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Update/Select [message #345456 is a reply to message #345448] Wed, 03 September 2008 12:10 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
But this is not working

But, it IS working. It does even MORE than you've expected!

Check example in message #345451 in this topic and research use of the EXISTS clause.
Re: Update/Select [message #345470 is a reply to message #345456] Wed, 03 September 2008 13:48 Go to previous message
RicM
Messages: 2
Registered: September 2008
Junior Member
Solution 1:
update t1 set t1.c1 = (select t2.c1 from t2
where t1.id = t2.id)
where exists (select t2.c1 from t2
where t1.id = t2.id);

Solution 2:
update t1 set t1.c1 = (select t2.c1 from t2
where t1.id = t2.id)
where t1.id in (select t2.id
from t2
where t1.id = t2.id);

-- Post closed --

Thank you for your help.

Cheers
Previous Topic: trigger issue
Next Topic: Help with date format picture ends ....error
Goto Forum:
  


Current Time: Tue Dec 06 12:19:03 CST 2016

Total time taken to generate the page: 0.10391 seconds