CODEWITHSUNDEEP
sqlsql-update
user1332821
user1332821

Reputation: 83

PL/SQL Update statement return multiple rows

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);

Upvotes: 0

Views: 3468

Answers (2)

Jeff Wu
Jeff Wu

Reputation: 2578

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.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

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?

Upvotes: 1

Related Questions