doubi6669
2017-06-20 18:20
浏览 339
已采纳

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

I'm having a hard time copying data from one row to another in the same table. Basically, I have a case where the column [I/O#] equals 'Client1' but I would like to make the [I/O#] column equal to 'Client2' without having the data attributed to Client1 erased, i.e. I want to keep the data associated with Client1 but just replace the name to Client2.

I attempted the following sql query in my php file but in vain :

$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'";

By the way $PrevIO and $IO are already defined in my php file (just not showing it) so that's not where the error lies.

Now, let's say $PrevIO='Client1' and $IO='Client2. How can I attribute all the data (row) in table [form_record] that belongs to Client1 and copy it to Client2 and then eventually delete Client1 or even better, simply replace simply the name but you guys know better. I thought that newdata could be used as an alias table for the same table form_record but it seems like it doesn't work.

I'm using Microsoft SQL 2005 Server.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duandao2083 2017-06-20 18:26
    已采纳

    That should do it, you only need to include the proper join in the table.

    $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'";
    
    已采纳该答案
    打赏 评论
  • doufei3561 2017-06-20 19:43

    your join condition is missing :

    $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'";
    
    打赏 评论

相关推荐 更多相似问题