June 5, 2008 at 6:02 am
I have a Select command which is extracting data from more than one table. I want to know how to construct an equivalent Update command to affect fields in more than one table.
Later I will also need Delete and Insert commands, but once I figure out the Update command I'm hoping that the others will fall into place.
Here is my source code (from Visual Web Developer Express 2005). The Update command shown below modifies a single table. It needs to change, but it's a starting point:
SelectCommand="SELECT Events.EventName, Events.Location,
Events.StartDate, Events.StartTime, Events.EventDescription,
Events.EventID, Travel.Travel, [Group Photos].ImageURL
FROM Events
INNER JOIN [Group Photos] ON Events.EventID = [Group Photos].EventID
INNER JOIN Travel ON Events.EventID = Travel.EventID
WHERE (Events.EventID = @EventID)"
UpdateCommand="UPDATE [Events] SET [EventName] = @EventName,
[Location] = @Location,
[StartDate] = @StartDate,
[EventDescription] = @EventDescription
WHERE [EventID] = @original_EventID"
How do I modify the syntax to update table-specific fields? Can I use something like:
UPDATE [table1] SET [table1.field1] = @field1, ..., [table2.field2] = @field2, ... [table3.field3] = @field3, ...
INNER JOIN [table2] ON [table1.IDfield] = [table2.IDfield]
INNER JOIN [table3] ON [table1.IDfield] = [table3.IDfield]
WHERE [table1.IDfield] = @original_IDfield
I don't know whether INNER JOIN is relevant for an Update command, or where to put it, nor whether you can put a table name prefix inside the square brackets, nor how to add further tables into the "UPDATE [table1]" part.
Can anyone help?
June 5, 2008 at 8:35 am
You have to write seprate UPDATE/DELETE statements to update/delete rows from each table.
INNER JOIN can be used in UPDATE/DELETE statements.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply