Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Irina25
Starting Member
3 Posts |
 Posted - 2008-03-04 : 20:59:14
|
| I have the table tbOld with columns as fieldID (PK), Class, Name, other1, other2, other3 and tbNew with the same structure (fieldID (PK), Class, Name, other1, other2, other3).Most Class-Name combinations are the same in both tables but have different FieldID, others are only in the 'tbNew' and absent in the tbOld, some are only in the 'tbOld' but we are not interested in those.We need to update data in tbNew for columns other1, other2, other3 and make them the same as in tbOld where possible (where class-Name combination is present in the tbOld)The update statement based on the join on two columns does not work (both columns are not PK, as FieldID is PK in each table, but we can't join on it)Any help is highly appreciated. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-04 : 21:08:59
|
Does this give you the correct results (as a select statement)Select new.fieldID, new.Class, new.[Name], new.other1, new.other2, new.other3 old.other1 as oldother1, old.other2 as oldother2, old.other3 as oldother3From tblNew new ,tblold oldWhere (RTRIM(new.Class) + RTRIM(new.[Name])) = (RTRIM(old.Class) + RTRIM(old.Name)) if the class/name combination is NOT unique in the table, this may be an issue for an update as multiple records in NEW would be updated on this type of match from OLD if they are not uniqueedited to fix my aliases
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
Irina25
Starting Member
3 Posts |
Posted - 2008-03-05 : 15:10:51
|
| Thank you very much, dataguru! The query works great and I was able to make all the updates without 'outer joins' !Irina |
|
|
|
|
|
|
|
|
Update with complicated join