如何使用内部联接进行更新
本文关键字:更新 何使用 内部 | 更新日期: 2023-09-27 18:25:25
我如何执行内部联接,因为我想从另一个名为"药物"的表中检索信息,该表的属性为"说明",而不是"位置说明"。我该怎么做?
public static void UpdateLocationDescription( int locationID, string description, SqlConnection connection, SqlTransaction transaction )
{
StringBuilder sqlString = new StringBuilder();
SqlCommand command;
sqlString.Append( "UPDATE [Location] SET " );
sqlString.Append( "description = @description " );
sqlString.Append( "WHERE locationID = @locationID " );
command = new SqlCommand( sqlString.ToString(), connection );
if( ( transaction != null ) ) command.Transaction = transaction;
command.Parameters.Add( "@locationID", SqlDbType.Int ).Value = locationID;
command.Parameters.Add( "@description", SqlDbType.VarChar ).Value = description;
int rowsAffected = command.ExecuteNonQuery();
if( !( rowsAffected == 1 ) )
{
throw new Exception( "An error has occurred while updating UpdateMedicationDispenseStatus." );
}
}
使用"FROM"关键字可以做到这一点。
UPDATE [Medication]
SET [Medication].description = @description
FROM [Medication] INNER JOIN [Location] ON [Medication].LocationID = [Location].LocationID
WHERE ...
因为我不知道你的结构或要求。但是您可以将它写成一个带有SELECT语句的INNER JOIN。您可以编写一个SELECT语句,其中包含要考虑的字段。然后将"SELECT[fields]"部分替换为"UPDATE[tableyouwantochange]SET[field]=[value]
要检索数据,只需使用一个新的SELECT语句。
你是这样的意思吗?
UPDATE [Location]
SET [Location].description = (SELECT top 1* m.description FROM Medication m WHERE m.LocationID =@LocationId)
WHERE Location.ID =@LocationId