[Solved] How to Update a field in a table

Creating tables and queries
Post Reply
Rich50
Posts: 2
Joined: Mon Apr 02, 2018 10:00 pm

[Solved] How to Update a field in a table

Post by Rich50 »

Very new to OpenOffice!
I am trying to write an update query to modify a field/column with the contents of another field in the same table

UPDATE "personMac" SET "YOB" = SELECT RIGHT( "Birth date", 4 ) FROM "personMac"
OR
UPDATE "personMac" SET "YOB" = (SELECT RIGHT( "Birth date", 4 ) FROM "personMac")

Both "Birth date" and "YOB" are Text fields. but Tools SQL dosent let this run.. keeps saying: Single value expected

Help Please ( I'm sure I'm not too far from getting it right!)
Thanks Rich
Last edited by Hagar Delest on Tue Apr 03, 2018 8:38 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.4 on MacOSX
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to Update a field in a table

Post by Villeroy »

UPDATE "personMac" SET "YOB" = RIGHT( "Birth date", 4 )
if YOB is a small integer:
UPDATE "personMac" SET "YOB" = CAST(RIGHT( "Birth date", 4 ) AS SMALLINT)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Rich50
Posts: 2
Joined: Mon Apr 02, 2018 10:00 pm

Re: How to Update a field in a table

Post by Rich50 »

Thanks Villeroy
I was making hard work of it!
OpenOffice 4.1.4 on MacOSX
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to Update a field in a table

Post by Villeroy »

Code: Select all

UPDATE "personMac" SET "YOB" = (SELECT RIGHT( "Other_Table"."Birth date", 4 ) FROM "Other_Table" WHERE "personMac"."X" = "Other_Table"."Y")
updates YOB with values from another table's column where this table's X equals Y but only if each X belongs to one Y. If more than one Y belongs some X you get the same error.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply