SQL Server 2000: Updating one table with values from another table

Question

I have a form that displays a list of systems along with their current status. The user can change the status and the date of that status change is stored in a history table. The user can also change the name of the server as a status changes (for example, if a system is replaced due to a lease roll).

The history table stores the details by systemname so if the system name changes on the form, the history also needs to be updated (along with all the historical changes). On the form, there is a hidden field named originalsystemName so we know if the systemname matches or not when the form is saved.

<input type='text' name='systemname'>
  <input type='text' name='originalSystemName'>
  <input type='text' name='status'><input type='submit' type='submit'>

I came up with the following query but I'm getting an error (see below the query).

update SysHistory set  
SystemName = (
    select distinct t.systemname 
    from systemInfo_tmp t, SysHistory h 
    where t.systemname != t.originalSystemName
)    
where systemname in (
    select distinct t1.originalSystemName 
    from systemInfo_tmp t1, SysHistory h1 
    where t1.systemname != t1.originalSystemName
)

Error I'm receiving:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Just an extra tidbit in case it was not obvious but the history table could have 1-100 entries so each entry needs to be updated if the system name changes (where systemname != originalSystemname). — HPWD, Feb 22 '12 at 21:00
In order to get accurate solutions, could you please provide a few rows of sample data before and after the update (also include at least one row that you do *not* want updated). Solving word problems is fun, but it leads to a lot of wasted effort here. — Aaron Bertrand, Feb 22 '12 at 21:13
@AaronBertrand - sorry about that. I'll be more mindful. Some of my questions get wordy so I try to be specific as possible. Thanks again, I do appreciate the help. You specifically have helped me a couple of times in the past. — HPWD, Feb 22 '12 at 21:31

score 5 · Answer 1 · answered Feb 22 '12 at 20:59

5

Is it possible that this is what you meant? Though unless this is a one-row table, there seems to be a WHERE clause missing.

UPDATE h
    SET SystemName = t.systemname
    FROM SysHistory AS h
    INNER JOIN systemInfo_tmp AS t
    ON h.systemname <> t.originalSystemName;

answered Feb 22 '12 at 20:59

Aaron Bertrand

272,866
37
466
490

My guess is that `systemInfo_tmp` may or may not be associated to a `systemName` that is changed. So by checking `t.originalSystemName <> t.systemName`, we know that the name has been updated... my next guess is that in order to associate the updated `systemName` with the `sysHistory`, we have to join on `h.systemName = t.originalSystemName`... but of course I'm just guessing. – Michael Fredrickson Feb 22 '12 at 21:12
@Michael Yes, this is where sample data and desired results would be very useful. – Aaron Bertrand Feb 22 '12 at 21:12
@AaronBertrand - I wasn't sure how to provide the sample data. The desired results is if on the form a system's name is changed, the system name in the status history table also needs to change. – HPWD Feb 22 '12 at 21:29
@dlackey So you couldn't show a few system names before the update, and then what they should be changed to? If you can't do that, how can you verify whether any solution is correct? I'm not trying to be mean but surely you understand where you are and where you want to get to - that information is always useful and prevents people from spinning their wheels, guessing, waiting for you to respond with more information, etc. – Aaron Bertrand Feb 22 '12 at 21:44
@AaronBertrand Would this have helped? Oringal Values: Alpha, Beta, Chi Desired Values: Alpha01, Beta01, Chi01 I'm trying to learn what you guys need to know so that I can get the help I need and you guys can get to the next person. Sincerely, DLackey – HPWD Feb 22 '12 at 22:49
@dlackey yes that and what information dictated that they should be appended with `01` - where does the `01` come from? That's a rhetorical question - don't answer it - just trying to help illustrate what questions we'll have. – Aaron Bertrand Feb 23 '12 at 00:31

score 2 · Accepted Answer · answered Feb 22 '12 at 20:59

To update a table with values from another table, this is easily accomplished with a join.

In this scenario, the sysHistory table is joined to the systemInfo_tmp table when the systemInfo_tmp contains a systemName that doesn't match the original, and the sysHistory's systemName matches.

update sh
set systemName = t.systemName
from
    sysHistory sh join
    systemInfo_tmp t on 
        t.originalSystemName = sh.systemName
        and t.originalSystemName != t.systemName

Thanks! I made this much harder than it needed to be. – HPWD Feb 22 '12 at 21:25 — HPWD, Feb 22 '12 at 21:25

SQL Server 2000: Updating one table with values from another table

2 Answers2