up vote 0 down vote favorite

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 quires cannot return more than one column.

Method2: 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 thats not the efficient way I guess.

share|improve this question
Search for answers in stackoverflow, before ask. Hope this question solve your problem: stackoverflow.com/questions/3736732/… – Senthil Apr 20 '11 at 2:09

2 Answers

up vote 0 down vote accepted 
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.

share|improve this answer
I was looking for this . Thanks a lot. – Divya Apr 23 '11 at 5:41
up vote 0 down vote

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

share|improve this answer
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 3:39
Thanks a lot for the answer – Divya Apr 23 '11 at 5:42

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.