PL/SQL Update statement return multiple rows

Question

I'm trying to update a name column from table test and update from column name2 from test2 table where another two column from both tables have to match.

It gives me error "single row query return multiple row".

How do I return multiple rows? Do I use a for loop? If so, how?

Update Test  
SET name = (Select name2
            from test2
            where name2.col2 = name.col)
WHERE EXISTS
  ( SELECT name2
    FROM test2
   WHERE name2.col2 = name.col);

In your sub-queries you check for name2.col2 and name.col, but name and name2 seem to be your column names, wile your table names are not used? Please try to fix your example... — Peter Lang
– Peter Lang, Commented Apr 27, 2012 at 14:42

Tony Andrews · Accepted Answer · 2012-04-27 09:34:13Z

1

Your problem is that you are using a scalar subquery to get a value to set the name to, but in fact your scalar subquery finds many values:

Select name2 from test2 where name2.col2 = name.col;

name2
-----
FRED
JOHN
MARY

This probably means your query is wrong. Given the above example, what would you want to update test2.name to?

answered Apr 27, 2012 at 9:34

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

1 Comment

user1332821 Over a year ago

Thanks for the help I just need a bit of a push in the right direction.

Jeff Wu · Accepted Answer · 2012-04-29 18:53:18Z

0

What you want to do here is JOIN the tables test and test2.

A SELECT statement would look like:

SELECT name, name2
FROM test INNER JOIN test2
      ON test.col = test2.col

(there is a typo in your query above where you write name2.col = name.col, I'm assuming). Run this select statement to make sure that the value you want to replace "name" with is in the "name2" column.

An UPDATE statement would look as follows:

UPDATE test
SET name = test2.name2
FROM test2
WHERE test2.col = test.col

The WHERE statement in this UPDATE statement acts as a JOIN for the two tables.

answered Apr 29, 2012 at 18:53

Jeff Wu

2,5881 gold badge23 silver badges25 bronze badges

Collectives™ on Stack Overflow

PL/SQL Update statement return multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related