Postgresql subquery fetching multiple rows

Question

I have a table (table2) with 2 columns: name and age. I have another table (table1) with columns name, age, value, type.

I want to update table1 with adding table2 values and for value=1 and type="abc".

I tried:

Method 1:

insert into table1(select * from table2), 1, 'abc';

But getting error at ',' before 1, saying sub queries cannot return more than one column.

Method 2:

CREATE TABLE table2
(
   name varchar(20),
   age varchar(20)
);

insert into table2 .... inserted some values

alter table table2 add "value" varchar(10);
alter table table2 add "name" varchar(20);

update table2 set value=1, name='abc';
insert into table1 select * from table2;

I am using PostgreSQL. Can any one help me how to solve the issue. Method 2 works but that's not the efficient way, I guess.

Search for answers in stackoverflow, before ask. Hope this question solve your problem: http://stackoverflow.com/questions/3736732/update-or-insert-multiple-rows-and-cols-from-subquery-in-postgresql — Senthil, Apr 20 '11 at 02:09

score 1 · Accepted Answer · answered Apr 23 '11 at 00:10

1

INSERT INTO table1
SELECT table2.*, 1, 'abc'
FROM table2;

The * notation is a shortcut for all the columns of the referenced tables, but you may still add more columns, such as constants, if you wish.

answered Apr 23 '11 at 00:10

Andrew Lazarus

18,205
3
35
53

score 0 · Answer 2 · edited Apr 23 '11 at 00:38

For Method 1: Because the subquery returns multiple values for a single insert, the error is showing up.

Method 2:

This method works because, you are creating Table 2 to be the same as Table 1, then you are inserting all the values from Table2 into Table1. If you are doing this, then why do you have to maintain two separate tables to hold the same data?

You can use a cursor to solve this issue.

CREATE PROCEDURE USER_DATA_CURSOR
AS
DECLARE MY_CURSOR 
FOR
Select name,age From table2

Open My_Cursor

DECLARE @name varchar(20), @age varchar(20)

Fetch NEXT FROM MY_Cursor INTO @name, @age
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)

insert into table1(name,age,type,value) values(@name,@age,'abc',1);


FETCH NEXT FROM MY_CURSOR INTO @name, @age 
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

Exec USER_DATA_CURSOR

The code is not specific to Postgresql, u might have to tweak it accordingly. Let me know if you have any more questions

Actually my problem is I have a table and for a specific value (column 4) have to add a type (column 3). So table 2 is the distinct values of name and age. For those name and age who does not have this type and value. I have to add new rows to the table for all the distinct combinations of name and age.So I extracted the distinct values of name and age and trying to add type and value and add again to the existing table. — Divya, Apr 20 '11 at 03:39

Postgresql subquery fetching multiple rows

2 Answers2