June 30, 2008 at 9:14 am
I am trying to update Column 'Answer' with a default value of Result only if tblA [Entered Date] is the same as tblB [Entered Date].
Unfortunately this script is updating all fields in the column and not the selected fields as in the query
Anyone have any ideas, where I am going wrong
UPDATE tblA
SET Answer = 'Result'
(SELECT tblA.Who
FROM tblA INNER JOIN
tblB ON tblA.[Entered Date] = tblB.[Entered Date])
June 30, 2008 at 9:24 am
Not exactly sure how SQL Server is interpreting your query, but you might want something more like this:
UPDATE dbo.tblA
SET Answer = 'Result'
FROM dbo.tblA INNER JOIN
dbo.tblB ON (tblA.[Entered Date] = tblB.[Entered Date]) )
If there is more to the code than that, I'd need to know more about what you are trying to do.
😎
June 30, 2008 at 9:35 am
Sorry missed a bit, here is the query I am using.
UPDATE dbo.tblOpenDaily
SET Who = 'Result'
(SELECT TOP (100) PERCENT dbo.tblOpenDaily.[Entered Date]
FROM dbo.tblOpenDaily INNER JOIN
dbo.tblEnteredDateTemp ON dbo.tblOpenDaily.[Entered Date] = dbo.tblEnteredDateTemp.[Entered Date])
The results returned are .....
(231334 row(s) affected)
(21328 row(s) affected)
Where I only want 21328 rows updated.
June 30, 2008 at 9:43 am
There looks to be a problem with this query:
UPDATE dbo.tblOpenDaily
SET Who = 'Result'
(SELECT TOP (100) PERCENT dbo.tblOpenDaily.[Entered Date]
FROM dbo.tblOpenDaily INNER JOIN
dbo.tblEnteredDateTemp ON dbo.tblOpenDaily.[Entered Date] = dbo.tblEnteredDateTemp.[Entered Date])
Try this:
UPDATE dbo.tblOpenDaily
SET Who = 'Result'
FROM
dbo.tblOpenDaily INNER JOIN
dbo.tblEnteredDateTemp ON (dbo.tblOpenDaily.[Entered Date] = dbo.tblEnteredDateTemp.[Entered Date])
😎
June 30, 2008 at 9:48 am
That works, thanks very much 😀
June 30, 2008 at 9:52 am
The reason for the problem with the original query is that it's actually two separate queries that run separately. First, it does an update of all rows. Then it separately does a select of just specific rows.
The suggested query merges the two together into one command. That's why it works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply