[Solved] How to Update a field in a table

Creating tables and queries

[Solved] How to Update a field in a table

Postby Rich50 » Mon Apr 02, 2018 10:20 pm

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
Rich50
 
Posts: 2
Joined: Mon Apr 02, 2018 10:00 pm

Re: How to Update a field in a table

Postby Villeroy » Tue Apr 03, 2018 12:02 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26250
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to Update a field in a table

Postby Rich50 » Tue Apr 03, 2018 12:30 am

Thanks Villeroy
I was making hard work of it!
OpenOffice 4.1.4 on MacOSX
Rich50
 
Posts: 2
Joined: Mon Apr 02, 2018 10:00 pm

Re: How to Update a field in a table

Postby Villeroy » Tue Apr 03, 2018 1:01 am

Code: Select all   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26250
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests