sql oracle using update

Question

I need to take data from one table to add to another table.

I got two SQL's:

SQL1:

select * from table1;

SQL2:

select * from table2 where coloumn1 = table1.coloumn6 (number)

table1 looks like:

enter image description here

For better understanding we call each coloumn coloumn1, coloumn2, ... coloumn9

table2 looks like:

enter image description here

For better understanding we call each coloumn coloumn1, coloumn2, ... coloumn13

What should happen in text

My SQL has to take the value from table1.coloumn6 (number) - checking if this value is given in table2.coloumn1

select * from table2 where coloumn1 = table1.coloumn6 (SQL2)

If yes it should update the data from table1.coloumn2 (varchar2) to table2.coloumn4 (varchar2).

That's nice. Good luck figuring it tout. Did you have a qusetion? — Marc B
– Marc B, Commented May 6, 2015 at 14:36
My question is how to solve this - any ideas or usefull tips. — piguy
– piguy, Commented May 6, 2015 at 14:39

Gordon Linoff · Accepted Answer · 2015-05-06 15:00:36Z

1

If I understand correctly, you want to update table1.column2 to the corresponding value in table2.column1 based on other conditions. Based on your description:

update table1
    column2 = (select column4 from table2 t2 where t2.column1 = table1.column6)
    where exists (select 1 from table2 t2 where t2.column1 = table1.column6);

answered May 6, 2015 at 15:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

piguy Over a year ago

I need the data from table1 in table2. What I wanted to say is that I need the data in table1.coloumn2 transfer to table2.coloum4. But my SQL should be only allowed to do this if table1.coloum6 = table2.coloum1

piguy Over a year ago

I am sorry for my English which makes it hard for you to understand

Mark · Accepted Answer · 2015-05-06 18:22:18Z

1

MERGE is good for updating/inserting tables based on other tables. Something like this should work (would be a bit more clear with distinctly-named columns). It also accepts an optional 'WHEN NOT MATCHED THEN' clause which lets you insert new records.

MERGE INTO table2
USING (SELECT column2, column6 FROM table1) table1
ON (table2.column1 = table1.column6)
WHEN MATCHED THEN
  update set column4 = column2;

answered May 6, 2015 at 18:22

Mark

464 bronze badges

Collectives™ on Stack Overflow

sql oracle using update

table1 looks like:

table2 looks like:

What should happen in text

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

table1 looks like:

table2 looks like:

What should happen in text

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related