[Solved] How to write an update query?

Creating tables and queries

[Solved] How to write an update query?

Postby HyanDirgal » Wed Apr 02, 2008 5:45 pm

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

Re: How to write an update query?

Postby apollo18 » Fri Apr 04, 2008 2:38 pm

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
 
Posts: 1
Joined: Fri Apr 04, 2008 2:35 pm

Re: How to write an update query?

Postby guyvallero » Sun Apr 13, 2008 8:24 pm

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

Re: How to write an update query?

Postby probe1 » Sun Apr 13, 2008 11:49 pm

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
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
probe1
 
Posts: 237
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand Asia

Re: How to write an update query?

Postby RAylsworth » Tue Apr 29, 2008 7:52 am

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

Re: How to write an update query?

Postby HyanDirgal » Wed Apr 30, 2008 5:32 pm

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

Re: How to write an update query?

Postby RAylsworth » Wed Apr 30, 2008 8:37 pm

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


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests