Home » SQL & PL/SQL » SQL & PL/SQL » need solution (oracle 10g)
need solution [message #338911] Wed, 06 August 2008 04:33 Go to next message
kuldip
Messages: 10
Registered: November 2007
Junior Member



I have 2 tables... Table_A and Table_B

I need to update Table_A.ColumnB with the values from Table_B.ColumnB where Table_A.ColumnA = Table_B.ColumnA. Here's what I've tried so far:

update Table_A
set Table_A.ColumnB = (select Table_B.ColumnB
where Table_A.ClolumnA = Table_B.ClolumnA)

Re: need solution [message #338919 is a reply to message #338911] Wed, 06 August 2008 04:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And? Did it return an error? Did your server explode?
What happened? What do you need from us?
Re: need solution [message #338923 is a reply to message #338919] Wed, 06 August 2008 04:54 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'll put my bet on TOO-MANY-ROWS.
Re: need solution [message #338927 is a reply to message #338911] Wed, 06 August 2008 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Mon, 14 April 2008 07:38
As already said to you:
read OraFAQ Forum Guide, especially "How to format your post?" section.

I add:
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.

Regards
Michel


Re: need solution [message #338929 is a reply to message #338923] Wed, 06 August 2008 04:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I reckon that either it set lots of values to NULL (possibly raising an error), or it caused cthonian abominations to inhabit the server room (fixed in 11g)
Re: need solution [message #338999 is a reply to message #338929] Wed, 06 August 2008 07:42 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Or overlooking the first problem, you cannot get TOO_MANY_ROWS or NULLs with invalid syntax right off the bat (ie. FROM is missing).
Re: need solution [message #339000 is a reply to message #338999] Wed, 06 August 2008 07:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
But that missing FROM is surely only a typo because the copy and paste of the actual session and the actual error didn't work. Very Happy
Re: need solution [message #339014 is a reply to message #338911] Wed, 06 August 2008 08:55 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
SQL> update table_a
     set field_a2 = (select filed_b2 from table_a where field_a1 = table_b.field_b1)
     where exists (select null from table_a where field_a1 = table_b.field_b1);


Let me check...
SQL> CREATE TABLE TEST_V003 ( EMP_ID VARCHAR2(3) primary key, SAL NUMBER(8,2));

Table created.

SQL> INSERT INTO TEST_V001 VALUES ('&A',&B);
Enter value for a: 1
Enter value for b: 8000
old   1: INSERT INTO TEST_V001 VALUES ('&A',&B)
new   1: INSERT INTO TEST_V001 VALUES ('1',8000)

1 row created.

SQL> /
Enter value for a: 2
Enter value for b: 9000
old   1: INSERT INTO TEST_V001 VALUES ('&A',&B)
new   1: INSERT INTO TEST_V001 VALUES ('2',9000)

1 row created.

SQL> COMMIT;

Commit complete.

SQL>  CREATE TABLE TEST_V002 ( ID VARCHAR2(3) primary key, SAL NUMBER(8,2));

Table created.

SQL> INSERT INTO TEST_V002 VALUES ('&A',&B);
Enter value for a: 1
Enter value for b: 4000
old   1: INSERT INTO TEST_V002 VALUES ('&A',&B)
new   1: INSERT INTO TEST_V002 VALUES ('1',4000)

1 row created.

SQL> /
Enter value for a: 2
Enter value for b: 9999
old   1: INSERT INTO TEST_V002 VALUES ('&A',&B)
new   1: INSERT INTO TEST_V002 VALUES ('2',9999)

1 row created.

SQL> /
Enter value for a: 3
Enter value for b: 9998
old   1: INSERT INTO TEST_V002 VALUES ('&A',&B)
new   1: INSERT INTO TEST_V002 VALUES ('3',9998)

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST_V001
  2  ;

EMP        SAL
--- ----------
1         8000
2         9000

SQL> ED
Wrote file afiedt.buf

  1* SELECT * FROM TEST_V002
  2  /

ID         SAL
--- ----------
1         4000
2         9999
3         9998


SQL> ed
Wrote file afiedt.buf

  1  update test_v002
  2       set sal = (select sal from test_v001 where emp_id = test_v002.id)
  3*      where exists (select null from test_v001 where emp_id = test_v002.id)
SQL> /

2 rows updated.

SQL> select * from test_v002;

ID         SAL
--- ----------
1         8000
2         9000
3         9998



Regards,
Oli

[Updated on: Wed, 06 August 2008 09:06]

Report message to a moderator

Re: need solution [message #339060 is a reply to message #339014] Wed, 06 August 2008 12:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@Olivia: you have been around here at OraFAQ now for some time. You started out as an poster asking help, asking questions. Now be honest: what way did you learn more, replies that provided you ready-made solutions or replies that asked you how you would do things, asking you to think and rethink your problem?
Re: need solution [message #339198 is a reply to message #339060] Thu, 07 August 2008 01:17 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Frank wrote on Wed, 06 August 2008 12:24
@Olivia: you have been around here at OraFAQ now for some time. You started out as an poster asking help, asking questions. Now be honest: what way did you learn more, replies that provided you ready-made solutions or replies that asked you how you would do things, asking you to think and rethink your problem?



Sorry, I will try to avoid providing any ready-made solutions next time.
The replies that asked me how I should do the things, asking me to rethink helped me a lot rather than the ready made answers.

Regards,
Oli

[Updated on: Thu, 07 August 2008 01:17]

Report message to a moderator

Previous Topic: How to solve the mentioned issue with a single query
Next Topic: Insert Blob
Goto Forum:
  


Current Time: Tue Dec 06 15:54:04 CST 2016

Total time taken to generate the page: 0.13392 seconds