在同一个表中将数据从一行复制到另一行SQL

问题描述：

我很难在同一个表中将数据从一行复制到另一行。基本上，我有一个案例，列[I/O#]等于'Client1'，但我想使[I/O#]列等于'Client2'没有归因于Client1擦除的数据，即我想保留与Client1相关的数据，但只是将名称替换为Client2 。在同一个表中将数据从一行复制到另一行SQL

我试图在我的PHP文件，但徒劳下面的SQL查询：

$sql_UPDATE_query ="UPDATE [$connectionInfo[Database]].[dbo].[form_record] 
        SET 
         [Last_Edit] = newdata.[Last_Edit], 
         [User_Name] = newdata.[User_Name], 
         [Computer_Name] = newdata.[Computer_Name], 
         [form_name] = newdata.[form_name], 
         [Date] = newdata.[Date], 
         [facility] = newdata.[facility], 
         [Count] = newdata.[Count], 
         [Start_Time] = newdata.[Start_Time], 
         [Stop_Time] = newdata.[Stop_Time], 
         [MW/Hrs_Start] = newdata.[MW/Hrs_Start], 
         [MW/Hrs_Stop] = newdata.[MW/Hrs_Stop], 
         [M³] = newdata.[M³], 
         [MCF] = newdata.[MCF], 
         [Litres] = newdata.[Litres], 
         [Description] = newdata.[Description], 
         [Lock] = newdata.[Lock] 
        FROM 
         (
         SELECT 
          [Last_Edit], 
          [User_Name], 
          [Computer_Name], 
          [form_name], 
          [Date], 
          [facility], 
          [Count], 
          [Start_Time], 
          [Stop_Time], 
          [MW/Hrs_Start], 
          [MW/Hrs_Stop], 
          [M³], 
          [MCF], 
          [Litres], 
          [Description], 
          [Lock] 
         FROM [$connectionInfo[Database]].[dbo].[form_record] 
         WHERE 
          [I/O#] = '$PrevIO' 
         ) newdata 
        WHERE 
         [I/O#] = '$IO'";

通过$PrevIO和$IO在我的PHP文件中已经定义的方式（只是没有显示它），所以这不是在错误所在。

现在，我们假设$PrevIO='Client1'和$IO='Client2。我如何将属于Client1的表[form_record]中的所有数据（行）归入并复制到Client2，然后最终删除Client1或甚至更好，只需简单地替换名称，但你们知道的更好。我认为newdata可以用作同一表form_record的别名表，但它似乎不起作用。

我正在使用Microsoft SQL 2005 Server。

答

这应该这样做，你只需要在表中包含正确的连接。

$sql_UPDATE_query ="UPDATE UPD 
        SET 
         [Last_Edit] = newdata.[Last_Edit], 
         [User_Name] = newdata.[User_Name], 
         [Computer_Name] = newdata.[Computer_Name], 
         [form_name] = newdata.[form_name], 
         [Date] = newdata.[Date], 
         [facility] = newdata.[facility], 
         [Count] = newdata.[Count], 
         [Start_Time] = newdata.[Start_Time], 
         [Stop_Time] = newdata.[Stop_Time], 
         [MW/Hrs_Start] = newdata.[MW/Hrs_Start], 
         [MW/Hrs_Stop] = newdata.[MW/Hrs_Stop], 
         [M³] = newdata.[M³], 
         [MCF] = newdata.[MCF], 
         [Litres] = newdata.[Litres], 
         [Description] = newdata.[Description], 
         [Lock] = newdata.[Lock] 
        FROM [$connectionInfo[Database]].[dbo].[form_record] UPD 
        INNER JOIN 
         (
         SELECT 
          [Last_Edit], 
          [User_Name], 
          [Computer_Name], 
          [form_name], 
          [Date], 
          [facility], 
          [Count], 
          [Start_Time], 
          [Stop_Time], 
          [MW/Hrs_Start], 
          [MW/Hrs_Stop], 
          [M³], 
          [MCF], 
          [Litres], 
          [Description], 
          [Lock] 
         FROM [$connectionInfo[Database]].[dbo].[form_record] 
         WHERE 
          [I/O#] = '$PrevIO' 
         ) newdata ON ***newdata.field = UPD.field*** 
        WHERE 
         [I/O#] = '$IO'";

它说以下错误信息：错误：附近有语法错误 '*'。查询... – Andrew

是的，你需要用你的条件替换'*** newdata.field = UPD.field ***'。 –

答

您join condition丢失：

$sql_UPDATE_query ="UPDATE A 
        SET 
         [Last_Edit] = newdata.[Last_Edit], 
         [User_Name] = newdata.[User_Name], 
         [Computer_Name] = newdata.[Computer_Name], 
         [form_name] = newdata.[form_name], 
         [Date] = newdata.[Date], 
         [facility] = newdata.[facility], 
         [Count] = newdata.[Count], 
         [Start_Time] = newdata.[Start_Time], 
         [Stop_Time] = newdata.[Stop_Time], 
         [MW/Hrs_Start] = newdata.[MW/Hrs_Start], 
         [MW/Hrs_Stop] = newdata.[MW/Hrs_Stop], 
         [M³] = newdata.[M³], 
         [MCF] = newdata.[MCF], 
         [Litres] = newdata.[Litres], 
         [Description] = newdata.[Description], 
         [Lock] = newdata.[Lock] 
        FROM [$connectionInfo[Database]].[dbo].[form_record] A 
        INNER JOIN 
         (
         SELECT 
          [Last_Edit], 
          [User_Name], 
          [Computer_Name], 
          [form_name], 
          [Date], 
          [facility], 
          [Count], 
          [Start_Time], 
          [Stop_Time], 
          [MW/Hrs_Start], 
          [MW/Hrs_Stop], 
          [M³], 
          [MCF], 
          [Litres], 
          [Description], 
          [Lock] 
         FROM [$connectionInfo[Database]].[dbo].[form_record] 
         WHERE 
          [I/O#] = '$PrevIO' 
         ) newdata ON newdata.field = A.field 
        WHERE 
         [I/O#] = '$IO'";

在同一个表中将数据从一行复制到另一行SQL

相关推荐