Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have the problem, that I have to do an insert into on table of my database and a update another table with the id, the insert provided me with. I tried this:

UPDATE events SET mail_key = (
INSERT INTO mail_keys 
    SELECT (name) FROM events)
RETURNING id);

But it doesn't work. Error is:

ERROR:  Syntax Error at »INTO«
LINE 2:  INSERT INTO mail_keys 

It is a bit simplified, since I do not select the name, but a normalized version of the name, which does not create the problem here.

Has anyone an idea? This statement will be duplicated over more tables (clubs, persons,...), so I have a unique key of serveral tables. The next question would be, how to ensure, that there are no double mail_keys created, if the event.name is not unique in events. I have a unique constraint in mail_keys.

EDIT (for more information): The mail_key table looks like this:

CREATE TABLE sailbook.mail_keys (
id serial,
mail_key character varying(127),
CONSTRAINT mail_key_pk PRIMARY KEY (id),
CONSTRAINT unique_mail_key UNIQUE(mail_key)
);

What I want to do is: I have a table with names of an event, like:

123 -  Hurricane
242 - Street Art
363 - The Party 2012

And Clubs like this:

896 - Town Hall Club
874 - Halo 12
846 - Francist

And I want to create a unique mail key out of it and store it in a different table (since the mail key should not only be unique for an event, but for the club or the person too). The mail key table would then look like this:

1 - hurricane
2 - street_art
3 - the_party_2012
4 - town_hall_club
5 - halo_12
6 - francist 

And the event table would look like this:

123 - Hurricane - 1
242 - Street Art - 2
363 - The Party 2012 - 3

And the club table would look like this:

896 - Town Hall Club - 4
874 - Halo 12 - 5 
846 - Francist - 6
share|improve this question
Your subquery is not scalar and it is not correlated to the main (update) query. What do you want to happen? (you could add some data to the question to illustrate your intentions) – joop 13 hours ago
ok, I edited the question for more information – DonMarco 13 hours ago
The events table is suddenly renamed into sailbook.mail_keys ? Are there two tables involved, or does the table serve as both source and target for the update ? – joop 13 hours ago

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.