OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

[SOLVED] UPDATE tables with 1-n relations

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
hwoehrle
General User
General User


Joined: 21 Sep 2009
Posts: 14
Location: Germany, Ulm
PostPosted: Mon May 17, 2010 9:09 am    Post subject: [SOLVED] UPDATE tables with 1-n relations Reply with quote
Hi,

i'm getting mad with the following problem.
i have two tables, linked like that:
table1.unitref <- n-1 -> unitdb.id

i want to update a field, for example
table.unittxt = unitdb.description

when using the following command:
UPDATE "table1" SET "unittxt"=(SELECT "unitdb"."description" FROM "unitdb", "table1" WHERE "table1.unitref" = "unitdb.id")
i get an error saying only one result is expected. Due to the n-1 relation i get, of course more than one result...

Can anyone help me with this?
Thanks in advance

Last edited by hwoehrle on Mon May 17, 2010 11:23 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Mon May 17, 2010 9:41 pm    Post subject: Reply with quote
hwoehrle:

You asked:

hwoehrle wrote:
Code:
UPDATE "table1" SET "unittxt"=(SELECT "unitdb"."description" FROM "unitdb", "table1" WHERE "table1.unitref" = "unitdb.id")

i get an error saying only one result is expected. Due to the n-1 relation i get, of course more than one result...

In your Select statement . . . you have TWO tables in the FROM portion:
  1. "unitdb"
  2. "table1"
But
  1. Since "table1" is already identified in the UPDATE portion, it should NOT be included in the FROM part of the SELECT statement.

  2. You did NOT correctly use the double quotes . . . you said . . . WHERE "table1.unitref" = "unitdb.id" and I suspect this should be . . . WHERE "table1"."unitref" = "unitdb"."id"

Therefore, try this:

Code:
UPDATE "table1" SET "unittxt"=(SELECT "unitdb"."description" FROM "unitdb" WHERE "table1"."unitref" = "unitdb"."id")

Please see the link below, at the bottom by VenturSum for a good 'example' of what you trying to do.

http://www.oooforum.org/forum/viewtopic.phtml?t=72966

One more request, please be sure to respond here, so others can learn from your progress. In your link at: http://www.oooforum.org/forum/viewtopic.phtml?p=361550 I took considerable time to answer your question, but, you never responded back. And, yes, just as you said, "I'm getting mad" that you have not responded. Smile

I hope this helps, please be sure to let me / us know. Smile

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
hwoehrle
General User
General User


Joined: 21 Sep 2009
Posts: 14
Location: Germany, Ulm
PostPosted: Mon May 17, 2010 11:22 pm    Post subject: Thanks a lot Reply with quote
Thank you very much for your precise and fast answer. It completly solved my problem!
Very Happy
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group