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.