UPDATE on table to INNER JOIN with same table.

Question

Post reply

UPDATE on table to INNER JOIN with same table.

Trystan Hughes

SSCrazy

Points: 2109
More actions
September 12, 2006 at 8:13 am

#113570

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 3

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

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