SQL update query

[hondaman2003]

I need a query that will update only the first record returned. In addition, I need to be able to sort so it will update only the lowest value for the primary key. Here is what I have so far and it is not working. I get the error 'Unable to parse query text.' when I am in query builder. When I debug the appliction I get a syntax error.

UPDATE TOP (1) [table name]
SET field1 = @LANID
WHERE (field2 = false) AND field3 = false) AND (field4 = false) AND (field5 = false) AND (field6 = false)
ORDER BY primary_key

How can I do this?

 

[hondaman2003]

I'm sorry, I thought I should let you know a few more details. This is a visual studio 2008 using VB application. I am connected to a MS Access database.

 

[gmmastros]

You're missing an open parenthesis before "field3" although I suspect this is just a typo.

I know that with SQL Server, you cannot use an order by in an update statement. I don't know how things are with Access.

Personally, I would probably do this in 2 steps.

1. Get the primary key to update.
2. Update the table.

Ex:

Select Top 1 primary_key
From   [TableName]
Where  (field2 = false) AND (field3 = false) AND (field4 = false) AND (field5 = false) AND (field6 = false)
Order BY primary_key

Update  [Table Name]
Set     field1 = @LANID
Where   primary_key = @PrimaryKey

In SQL Server, you could combine the statements like this:

UPDATE [table name]
SET field1 = @LANID
Where  primary_key = (
  Select Top 1 primary_key
  From   [table name]
  WHERE (field2 = false) AND (field3 = false) AND (field4 = false) AND (field5 = false) AND (field6 = false)
  ORDER BY primary_key)

I don't know if this query will work in Access, but I would be willing to bet that if it doesn't, there is probably similar syntax that would work.

Hope it helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Out of curiosity, if you are selecting the top 1 (thus only 1 line is returned), is having the 'order by' included in the query not redundant? Why do you need to order the results for only one returned result line?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

Bluejay07,

The order by is important when using the TOP clause. Without the order by, there is no guarantee that you get the data you actually want. In fact, I would go as far to say... any time you use the TOP clause, you should also use an order by.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Thanks for the clarification.

If at first you don't succeed, then sky diving wasn't meant for you!