Apache OpenOffice Community Forum

I am trying to write a query that will update a date field in a table. I read in other posts that I can do this through the SQL view, but I am having trouble finding the right syntax.

I tried the following:
update "tblRawFamilyInfo" set "DateExported" = CURDATE() where "DateExported" is null

When the Run SQL Command Directly button is selected, I get the following error: The data content could not be loaded. Statement does not generate a result set
When the button is not selected, I see this: The given command is not a select statement. Only queries are allowed.

I played with the syntax: adding a semicolon at the end of the statement, replacing Null with Empty, replacing CURDATE() with NOW(), even hard-coding the date. Anyone have an idea what I am doing wrong?

Thanks.

The SQL view of the query design window does not support update queries. To run an update query, you need to click Tools: SQL from the main window.

apollo18 wrote:The SQL view of the query design window does not support update queries. To run an update query, you need to click Tools: SQL from the main window.

Fair enough, but how do I know whether/how the UPDATE query succeeded if that Tools: SQL does not display a result set?

And, assuming I created a bug-free UPDATE query using that Tools: SQL window, how do I save it?

Thanks,
Guy

The status box will display
1: Command successfully executed.


Save your commands by copying them to a document.

It's neither "nice" nor convenient - but it works.

Full featured Database engines (I'm using MySQL and PostgreSQL) come with command line tools (sometimes with GUIs), which allows command histories or running files with SQL commands.

HTH

I have discovered, purely by accident, that if you include the primary key in a query of a single table, the table fields can be updated.
I am still trying to figure out how to update fields in a view of joined tables.

Thanks for all the replies.

RAylsworth, are you referring to update queries in the query design window, or in the Tools:SQL window? If the query design window, can you give me an example of a query including the primary key? I tried putting the key in the where clause, but that didn't help.

The query was created with Design View, I am not an SQL programmer.

Table: MailingList
Primary Key, Field Name = ID, Field Type = Integer, AutoValue = Yes
...
Query: 2008 Mailing List
SELECT "ID", "FamName", "FirstName", "Address", "City", "St", "Zip", "Phone", "Since", "MemDate", "Caller", "Board", "Donor" FROM "MailingList" WHERE ( ( "MemDate" > 2004 ) OR ( "Board" > 0 ) OR ( "First Mailing" > 2004 ) ) ORDER BY "FamName" ASC, "FirstName" ASC

With this query I can enter or update field data, as well as add and delete Table records.
Hope this helps.