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.
[Solved] How to write an update query?
-
- Posts: 3
- Joined: Wed Apr 02, 2008 5:26 pm
[Solved] How to write an update query?
Last edited by HyanDirgal on Wed Apr 30, 2008 10:11 pm, edited 1 time in total.
Re: How to write an update query?
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.
-
- Posts: 11
- Joined: Mon Apr 07, 2008 8:13 am
Re: How to write an update query?
Fair enough, but how do I know whether/how the UPDATE query succeeded if that Tools: SQL does not display a result set?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.
And, assuming I created a bug-free UPDATE query using that Tools: SQL window, how do I save it?
Thanks,
Guy
a signature
Re: How to write an update query?
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
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
-
- Posts: 13
- Joined: Tue Apr 29, 2008 7:39 am
Re: How to write an update query?
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.
I am still trying to figure out how to update fields in a view of joined tables.
Ray A.
-
- Posts: 3
- Joined: Wed Apr 02, 2008 5:26 pm
Re: How to write an update query?
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.
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.
-
- Posts: 13
- Joined: Tue Apr 29, 2008 7:39 am
Re: How to write an update query?
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.
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.
Ray A.