Home » SQL & PL/SQL » SQL & PL/SQL » Update table c1 for all entries that matches table c2 based on matching column a (oracle 9i)
Update table c1 for all entries that matches table c2 based on matching column a [message #434144] Mon, 07 December 2009 07:45 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
sql> select * from c1;

         A          B          C
---------- ---------- ----------
         1          0          0

Elapsed: 00:00:00.00
sql> select * from c2;

         A          B          C
---------- ---------- ----------
         1          1          0
         1          2          1
         1          3          2
         1          4          3
         1          5          4
         1          6          5
         1          7          6
         1          8          7
         1          9          8
         1         10          9
         2          1          0

         A          B          C
---------- ---------- ----------
         2          2          1
         2          3          2
         2          4          3
         2          5          4
         2          6          5
         2          7          6
         2          8          7
         2          9          8
         2         10          9
         3          1          0
         3          2          1

         A          B          C
---------- ---------- ----------
         3          3          2
         3          4          3
         3          5          4
         3          6          5
         3          7          6
         3          8          7
         3          9          8
         3         10          9

30 rows selected.


I want a result like this:

select * from c1;
         A          B          C
---------- ---------- ----------
         1          1          0
         1          2          1
         1          3          2
         1          4          3
         1          5          4
         1          6          5
         1          7          6
         1          8          7
         1          9          8
         1         10          9


How can I do this? Can I update table c1 for c1=1 without using cursor?
Many thanks
Re: Update table c1 for all entries that matches table c2 based on matching column a [message #434145 is a reply to message #434144] Mon, 07 December 2009 07:52 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
I fear that you have over simplified the issue here. Are you saying that you simply want table c1 to contain the values that are currently in c2 where column a = 1?

Re: Update table c1 for all entries that matches table c2 based on matching column a [message #434146 is a reply to message #434145] Mon, 07 December 2009 07:56 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
yes but in table c1 there may be other values for a=2,3,4

select * from c1;
         A          B          C
---------- ---------- ----------
         1          0          0
         2          0          0
         3          0          0
         4          0          0


after update it should be:
sql> select * from c1;

         A          B          C
---------- ---------- ----------
         1          1          0
         1          2          1
         1          3          2
         1          4          3
         1          5          4
         1          6          5
         1          7          6
         1          8          7
         1          9          8
         1         10          9
         2          1          0

         A          B          C
---------- ---------- ----------
         2          2          1
         2          3          2
         2          4          3
         2          5          4
         2          6          5
         2          7          6
         2          8          7
         2          9          8
         2         10          9
         3          1          0
         3          2          1

         A          B          C
---------- ---------- ----------
         3          3          2
         3          4          3
         3          5          4
         3          6          5
         3          7          6
         3          8          7
         3          9          8
         3         10          9
         4          0          0


[Updated on: Mon, 07 December 2009 08:00]

Report message to a moderator

Re: Update table c1 for all entries that matches table c2 based on matching column a [message #434147 is a reply to message #434146] Mon, 07 December 2009 07:58 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
What have you tried?
Re: Update table c1 for all entries that matches table c2 based on matching column a [message #434149 is a reply to message #434147] Mon, 07 December 2009 08:09 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Do I need to insert for matching rows (c1.a=c2.a) and then delete rows the previous row? The table c1 is temporary table

[Updated on: Mon, 07 December 2009 08:09]

Report message to a moderator

Re: Update table c1 for all entries that matches table c2 based on matching column a [message #434150 is a reply to message #434146] Mon, 07 December 2009 08:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
INSERT INTO c1
SELECT a,b,c
FROM   c2
WHERE  c2.a IN (SELECT c1.a 
                FROM   c1);

DELETE c1
WHERE  b=0
AND    c=0
AND    a IN (SELECT c2.a
             FROM   c2);
Previous Topic: anything like using exists
Next Topic: Number of unique keys in the table
Goto Forum:
  


Current Time: Tue Sep 27 05:56:04 CDT 2016

Total time taken to generate the page: 0.12208 seconds