Problem with replacing values | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem with replacing values

Hi All, I am writing a small query and having some problem Table1 EmpName BooksRead TimeTaken —————- —————— —————- Ram HarryPotter 15 Ram Water 79 Ram Running Train 100 EmpName BooksRead TimeTaken —————- —————— —————- Shyam under Siege 121 Shyam Computers 231 Shyam India Today 24 Shyam FrontLine 54 There is a table say Table1. I want to update records for Books read and time taken for Ram with that of Shyam Hence my result shoud be: EmpName BooksRead TimeTaken —————- —————— —————- Ram under Siege 121 Ram Computers 231 Ram India Today 24 Ram FrontLine 54 I think we can do it with a simple select statement. Please do not use temporary tables to insert and pull the data from there………..
Please help me. This is urgent. Thanks, SK. Thanks, StarWarsBigBang
Look up GROUP BY in Books OnLine. It’s not that difficult, really. SELECT ‘RAM’ EmpName, T1.BooksRead, SUM(T1.TimeTaken)
FROM dbo.Table1 T1
WHERE T1.EmpName = ‘RAM’ OR T1.EmpName = ‘SHYAM’
GROUP BY T1.BooksRead
HAVING COUNT(*) = 2 If you want to include books that either Ram or Shya has read, but not necessarily both of them, then drop the HAVING clause.
If I got right what you want:
begin transaction
delete from table1 where empName = ‘Ram’
if @@error <> 0 begin
rollback transaction
break
end
update table1
set empName = ‘Ram’
where empName = ‘Shyam’ if @@error = 0 commit transaction
else rollback transaction

SK: please be more precise in describing what you want to do. First you say you want to update, in the end you say you want to use a SELECT statement.
I guess both the solutions do not match my requirements although mmarovic was quite close to it. I shouldn’t delete the entries for any of them. Actually I need this to write a stored procs where I will be having 2 input parmeters. First one will be say @srcEmpname and the other will be @dstnEmpName. I should’t delete the values for ‘Ram’ Let me tell my question again. I simply want to replace the values of ‘Books Read’ and ‘Time Taken’ for Ram with that of Shyam. My results should look like:
EmpName BooksRead TimeTaken —————- —————— —————- Ram under Siege 121 Ram Computers 231 Ram India Today 24 Ram FrontLine 54 Now when I select from this table, I should still see values for Shyam. Thanks, StarWarsBigBang
begin transaction
delete from table1 where empName = ‘Ram’
if @@error <> 0 begin
rollback transaction
return
end
insert into table1(EmpName, BooksRead, TimeTaken)
select ‘Ram’, BooksRead, TimeTaken
from table1
where empName = ‘Shyam’ if @@error = 0 commit transaction
else rollback transaction

From what you say, I understand that you want to show the name of employee X with the details for employee Y. In the real world, that would be fraud.
>>I want to update records for Books read and time taken for Ram with that of Shyam What do you want to do this? Madhivanan Failing to plan is Planning to fail

I am writing a stored proc, which would copy over the details of one region Id to another. This is going to be a real time scenario and hence the issue. As input parameter I am going to give @srcEmpName and @dstnEmpName where @srcEmpName will be the source employee name whose records are going to be copied and @dstnEmpName will the destination employee name. All records corresponding to @srcEmpName will be copied to records of @dstnEmpName. In the meanwhile let me try out mmarovic’s soln in my stored proc.
Thanks, StarWarsBigBang
Okay, so you’re dealing with a data conversion where you need to match old and new keys. No fraud involved.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
select @dstnEmpName, BooksRead, TimeTaken
from yourtable
where Empname = @srcEmpName
KH
I tried out what mmarovic said and it worked. What khtan says will also work, I guess.<br /><br />Adriaan – This is a practical situation. Imagine a table which has 2 fields: Employee name and employee responsibilities. Say, if employee A leaves the organisation and employee B is expected to take up his responsibilities then we would need such a proc so that the table contains employee B as name and his responsibilities as that of employee A<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />Thanks everyone…………..I have sent the code for testing now.<br /><br /><br />Thanks,<br /><br />StarWarsBigBang
]]>