[Solved] How to write an update query?

Creating tables and queries
Post Reply
HyanDirgal
Posts: 3
Joined: Wed Apr 02, 2008 5:26 pm

[Solved] How to write an update query?

Post by HyanDirgal »

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.
Last edited by HyanDirgal on Wed Apr 30, 2008 10:11 pm, edited 1 time in total.
apollo18
Posts: 1
Joined: Fri Apr 04, 2008 2:35 pm

Re: How to write an update query?

Post by apollo18 »

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.
guyvallero
Posts: 11
Joined: Mon Apr 07, 2008 8:13 am

Re: How to write an update query?

Post by guyvallero »

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
a signature
User avatar
probe1
Volunteer
Posts: 277
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand

Re: How to write an update query?

Post by probe1 »

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
Cheers
Winfried

DateTime2 extension: insert date, time or timestamp, formatted to your needs
RAylsworth
Posts: 13
Joined: Tue Apr 29, 2008 7:39 am

Re: How to write an update query?

Post by RAylsworth »

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.
Ray A.
HyanDirgal
Posts: 3
Joined: Wed Apr 02, 2008 5:26 pm

Re: How to write an update query?

Post by HyanDirgal »

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
Posts: 13
Joined: Tue Apr 29, 2008 7:39 am

Re: How to write an update query?

Post by RAylsworth »

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.
Ray A.
Post Reply