Updating one table based on the data in another

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating one table based on the data in another

Author

Topic

dallardyce
Starting Member

3 Posts

Posted - 2007-07-25 : 10:59:37

Hi,

I hope someone can help me. I've had a look around the forum for a solution but need a little more help. I have two tables:


Organisation (ORG_ID, EMPLOYEE_BAND)
Import (ORG_ID, NO_OF_EMPLOYEES, EMPLOYEE_BAND)

Basically Import contains a subset of the data in Organisation. Typical data in Import would be:


ORG_ID   NO_OF_EMPLOYEES   EMPLOYEE_BAND
------------------------------------------
1234     3855              10: 3001-5000
1235     15                01: 1-20
1236     143               04: 101-200

I want to update Organisation with the data in Import, where there is a match between the two tables. That is, update ORG_ID 1234 in Organisation with the EMPLOYEE_BAND data against ORG_ID 1234 in Import.

I've tried the following suggestion to a similar problem on this forum:


UPDATE o
SET o.EMPLOYEE_BAND = i.EMPLOYEE_BAND
FROM Organisation as o
JOIN Import AS i
ON o.ORG_ID = i.ORG_ID

...but I'm not sure if it's 100% correct as it seems to have updated more rows in Organisation than there are rows in Import with data in EMPLOYEE_BAND. I think that might be because of the join (and possibly because there is no WHERE clause).

Any suggestions as to how this could be achieved would be appreciated.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-26 : 07:43:47

"seems to have updated more rows in Organisation than there are rows in Import "

What do you mean by SEEMS. Either it did, or it did not.

--prepare tables and data

declare @Org table (ORG_ID int, EMPLOYEE_BAND varchar(20))
declare @Imp table (ORG_ID int, NO_OF_EMPLOYEES int, EMPLOYEE_BAND varchar(20))

insert into @Org select 1234,'test foo 1' 
union all select 1235,'test foo 2'
union all select 1236,'test foo 3'
union all select 1237,'test foo 4'

insert into @imp select 1234,3855,'10: 3001-5000'
union all select 1235,15,'01: 1-20'
union all select 1236,143,'04: 101-200'



--update them

UPDATE o 
SET o.EMPLOYEE_BAND = i.EMPLOYEE_BAND
FROM @Org o
INNER JOIN @imp i
ON o.ORG_ID = i.ORG_ID


-- show results

select * from @org

RESULTS:

ORG_ID  EMPLOYEE_BAND
------  -------------
1234	10: 3001-5000
1235	01: 1-20
1236	04: 101-200
1237	test foo 4

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dallardyce
Starting Member

3 Posts

Posted - 2007-07-26 : 08:27:39

Thanks for your reply. There are around 40,000 records in Import that have a value in EMPLOYEE_BAND, but the update statement on Organisation updated over 60,000 records. I'm not sure why this happened - as each ORG_ID is unique.

Is the theory behind the update correct? Is there a better way of achieving the end result?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 09:05:02

select org_id from organization
group by org_id having count(*) > 1

select org_id from import
group by org_id having count(*) > 1

E 12°55'05.76"
N 56°04'39.42"

dallardyce
Starting Member

3 Posts

Posted - 2007-07-26 : 10:31:01

Thank you both for your help - I think I have this nailed now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources