Update multiple rows with 1 query
Good mornning, I am new to MS SQL and my instructor recommend that I ask for help here. I appologize if this question has already been answered, I couldnt find a solution that works.
I have a page in php that queries table A (RPAssignmentTemp) and for every row it retrieves, it matches the same row in table B (RPAssignment) and updates it. There could be 50 updates, or there could be 1. Here is that code:
$query_selectd = "SELECT * FROM RPAssignmentTemp WHERE ((RPAssignmentTempDate = '$thisDay') AND (Identifier = 'query_E'))";
$result_selectd = odbc_exec($connect, $query_selectd);
while(odbc_fetch_row($result_selectd)) {
$BaselineLaborHrs = odbc_result($result_selectd, "BaselineLaborHrs");
$AssignmentID = odbc_result($result_selectd, "AssignmentID");
$query_E = "UPDATE RPAssignment SET BaselineLaborHrs = '$BaselineLaborHrs' WHERE (AssignmentID = '$AssignmentID')";
# perform the query
$ResultE = odbc_exec($connect, $query_E);
// Check result
if (!$ResultE) { echo " Invalid query E: " . odbc_error() . "<br>\n"; }
}
And here is an example of the data in table A:
SELECT * FROM RPAssignmentTemp WHERE RPAssignmentTempDate = '2010-08-11'
#4: 1 - 200.0000 - 7C34EF002F7D467DABCC9F6A43C4C2A0 - 2010-08-11 - 2010-08-11 09:49:52.000 - query_E
#4: 2 - 15.0000 - D9B230F0E95B4A58B4473747B1F0FB3F - 2010-08-11 - 2010-08-11 09:49:52.000 - query_E
#4: 3 - 10.0000 - EDEDD23E9CDC4D49975D8EF561BA92FE - 2010-08-11 - 2010-08-11 09:49:52.000 - query_E
#4: 4 - 50 - 5207D24EAF524E3B9B62D738C08D5135 - 2010-08-11 - 2010-08-11 09:49:52.000 - query_E
This works, but the problem is that my boss wants me to do it all in SQL, and thats where I am having trouble.
I tried this:
update RPAssignment
SET
RPAssignment.BaselineLaborHrs = RPAssignmentTemp.BaselineLaborHrs
FROM RPAssignment, RPAssignmentTemp
where (
(RPAssignment.AssignmentID = RPAssignmentTemp.AssignmentID) AND
((RPAssignmentTemp.RPAssignmentTempDate = substring(convert(varchar(20),getdate(),20),1,10)) AND (RPAssignmentTemp.Identifier = 'query_E'))
)
Which didnt work, so I did more research and tried this:
update RPAssignment
SET
BaselineLaborHrs = ( SELECT RPAssignmentTemp.BaselineLaborHrs
FROM RPAssignmentTemp
WHERE RPAssignmentTemp.AssignmentID = RPAssignment.AssignmentID)
WHERE EXISTS
( SELECT RPAssignmentTemp.RPAssignmentTempID
FROM RPAssignmentTemp
WHERE (RPAssignmentTemp.RPAssignmentTempDate = substring(convert(varchar(20),getdate(),20),1,10)) AND (RPAssignmentTemp.Identifier = 'query_E')
)
It wouldnt work until I replaced the first subquery:
update RPAssignment
SET
BaselineLaborHrs = ( SELECT RPAssignmentTemp.BaselineLaborHrs
FROM RPAssignmentTemp
WHERE RPAssignmentTemp.AssignmentID = RPAssignment.AssignmentID)
WHERE EXISTS
( SELECT RPAssignmentTemp.RPAssignmentTempID
FROM RPAssignmentTemp
WHERE (RPAssignmentTemp.RPAssignmentTempDate = substring(convert(varchar(20),getdate(),20),1,10)) AND (RPAssignmentTemp.Identifier = 'query_E')
)
with this:
update RPAssignment
SET
BaselineLaborHrs = ( SELECT RPAssignmentTemp.BaselineLaborHrs
FROM RPAssignmentTemp
WHERE RPAssignmentTemp.AssignmentID = 0)
WHERE EXISTS
( SELECT RPAssignmentTemp.RPAssignmentTempID
FROM RPAssignmentTemp
WHERE (RPAssignmentTemp.RPAssignmentTempDate = substring(convert(varchar(20),getdate(),20),1,10)) AND (RPAssignmentTemp.Identifier = 'query_E')
)
and though it works, it doesnt match the data and replace the correct values. Any help or idea would be greatly appreciated.
-Jon
