UPDATE on table to INNER JOIN with same table.
-
Hi all,
I am not sure if this is at all possible, but what I want to do is to be able to update a column for X amount of rows where the Date column of the record for the passed in ID is equal to the rows that I want to update and has the value of another column set to 1. (I can't use sub-selects as its for mobile).
What I have so far is as follows...
UPDATE tbl_NSP_Inspection INS1
SET INS1.IsLastOfDay = 0
FROM tbl_NSP_Inspection AS INS1 INNER JOIN tbl_NSP_Inspection AS INS2 ON (INS1.InspectionUID = INS2.InspectionUID)
WHERE (Date logic goes here)
AND (INS1.InspectionUID = @InspectionUID)
But this is giving me errors for some reason.
Hope you can help with this as I feel I am almost there, but am missing that little something
-
Have you tried this?
UPDATE INS1
SET INS1.IsLastOfDay = 0
FROM tbl_NSP_Inspection AS INS1 INNER JOIN tbl_NSP_Inspection AS INS2 ON (INS1.InspectionUID = INS2.InspectionUID)
WHERE (Date logic goes here)
AND (INS1.InspectionUID = @InspectionUID)
-
Trystan
Almost right. Take the table name out of the first line to leave just the alias:
UPDATE INS1
SET INS1.IsLastOfDay = 0
FROM tbl_NSP_Inspection INS1
INNER JOIN tbl_NSP_Inspection INS2
ON INS1.InspectionUID = INS2.InspectionUID
WHERE (Date logic goes here)
AND INS1.InspectionUID = @InspectionUID
John
-
Hi, and thanks for the replies.
That seems to work on the desktop but not on my SQL CE Mobile for some reason. Maybe it doens;t like INNER JOINs on UPDATE statements.
Another problem with it is that it is only updating the row that has the InspectionUID that is equal to @InspectionUID, what I need to do is use the InspectionUID to get the CreatedDate value and to then update the rows that have the same CreatedDate value...
Thanks
-
The answer is very simple in ANSI SQL JOINs are not updatable but Subqueries are so see if you can convert the JOIN to a Subquery. The code you got was T-SQL letting you update JOIN technically illegal operation. Hope this helps.
Kind regards,
Gift Peddie -
Something like this?
WHERE ins1.createdate IN
(SELECT createdate FROM tbl_NSP_Inspection
WHERE InspectionUID = @InspectionUID )
I'm afraid I can't help you on the SQL CE side of things.
John
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply