Simple Update Query..... (1 Viewer)

[ajb_1976]

Hi,

I am new to SQL server but have had a bit of exposure to MS Access.

I am trying to run a simple update query on single table (dbo.tblClient). I ran the code as a SELECT script and it produces the correct results, but when I change this to an UPDATE i get a message informing me that the 'subquery returned more than 1 value....'.

Therefore my question is - How do I update multiple records within the table using an UPDATE query?

The code I am using is this -

UPDATE dbo.tblClient
SET ClientCategoryID = 23
WHERE EnquiryNotes like '%Category=UK%'
AND ClientCategoryID = 0
AND EntryDate >= DATEADD(day,-7, GETDATE())

and the successful SELECT query is this -

SELECT ClientID, ClientCategoryID, EntryDate
FROM dbo.tblClient
WHERE EnquiryNotes like '%Category=UK Removals%'
AND ClientCategoryID = 0
AND EntryDate >= DATEADD(day,-7, GETDATE())

Many Thanks
Alan

 

[Ranman256]

1st: DATEADD("D",-7, GETDATE())
(unless day is a constant = "D")

2. save the select query to say Q1
then Q2 is the update query, "update Q1 set ClientCatID=23 "

 

[Minty]

Bit late to the party with this one.
I can't see anything wrong with that as T-SQL Update query.

The only thing I'm slightly suspicious of in the Criteria for Enquiry Notes
EnquiryNotes like '%Category=UK%'

I'm wondering if it's trying to evaluate that somehow.
Try hard coding a specific value in there instead as an experiment.

 

[WayneRyan]

I'm also late to the party.

The error message seems invalid because ... there is no subquery!
The SQL shown can't produce that message.
The SQL as shown should result in a message of some number of rows affected.
Any message involving "subquery" can only result from a trigger on the table.

The functional SELECT statement shows that all of the table/column names are correct.
Where was the query run? (SSMS, Access)
Was it possibly run in SSMS with other queries UNINTENTIONALLY run? (highlight only your query)

We need more info.

Wayne

 

[Isaac]

Was it possibly run in SSMS with other queries UNINTENTIONALLY run? (highlight only your query)

That's kind of what I was thinking too! The query looks fine to me?

But the question of where it was run is good too. Access is horrible at returning actual database server errors as a calling app. I'd be curious to see a screenshot of the ssms window...

 

[WayneRyan]

Isaac,

We're just gonna have to wait on the OP.

That message could very well come from a trigger.
A trigger that was written for single-row updates !!!
It might do a nested select from INSERTED that would do return this type of error.

But, that would be VERY evident in the error message shown by SSMS.
It would explicitly say trigger.

Guess we'll just wait and see.

Wayne

 

[isladogs]

1. Shouldn't the WHERE clause be the same as the SELECT query ...UK Removals%
2. Possibly unnecessary but try setting the query to show unique records. UPDATE DISTINCTROW ...

 

[WayneRyan]

Good catch isladogs.
My brain wanted to see them as the same.

But that would only affect the total number of rows affected.
Error messages would be PK or unique indexes ... not sub query returns too many rows.

Still a mystery.

Wayne