Updating Linked Tables - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Updating Linked Tables

Author

Topic

spc2000uk
Starting Member

3 Posts

Posted - 2012-03-05 : 10:06:44

I have 2 tables with the following columns

SERVERLICENSE
licenseid,accountid,licensekey,createddate,status (active or dormant)

TOOLICENSE
toollicenseid,licenseid,toolname,licensekey,createddate

The tables are linked via the licenseid. Typical data is:

SERVERLICENSE
1,201,1235-2563,10/01/11,dormant
2,201,5685-5365,05/01/12,active
3,202,7876-7875,10/11/12,active

TOOLLICENSE
1001,1,smtp,adfg-fgbh,10/01/11
1002,1,odbc,erth-bgfh,10/01/11
1003,1,html,rfgt-dsww,10/01/11
1004,2,word,edfg-tghj,05/01/12
1005,2,smtp,wwsk-plon,05/01/12
1006,3,smtp,fghg-gfde,10/11/12

From this data you can see that some of the tools are linked to Serverlincense 1, others are against serverlicense 2, and an updated toollicense has been created against serverlicense 2.

I want to update the toollicense table (or create a new one) that combines the distinct tools and latest updated tools and links them to the latest server license. This means I will end up with the folloing TOOLLICNSE table

TOOLLICENSE (NEW)
1002,2,odbc,erth-bgfh,10/01/11
1003,2,html,rfgt-dsww,10/01/11
1004,2,word,edfg-tghj,05/01/12
1005,2,smtp,wwsk-plon,05/01/12
1006,3,smtp,fghg-gfde,10/11/12

So the odbc & html tools have bbeen updated to link to serverlicense 2, and the earlier smtp tool has not been imported into the new table and serverlicense 3 entry has been import untouched.

Hope this makes sense...

Thanks

Steve

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-03-06 : 11:51:33

Perhaps its too late in the day but I'm afraid I don't follow what you're trying to achieve here. If anyone else fancies a crack at this though, I've thrown together the DDL to build the practice tables based on the data and information supplied.

CREATE TABLE SERVERLICENSE (licenseid INT,accountid INT,licensekey VARCHAR(12),createddate VARCHAR(12),status VARCHAR(10))
INSERT INTO SERVERLICENSE 
SELECT 1,201,'1235-2563','10/01/11','dormant' UNION
SELECT 2,201,'5685-5365','05/01/12','active' UNION
SELECT 3,202,'7876-7875','10/11/12','active' 

CREATE TABLE TOOLLICENSE (toollicenseid INT,licenseid INT,toolname VARCHAR(10),licensekey VARCHAR(12),createddate VARCHAR(10))
INSERT INTO TOOLLICENSE 
SELECT 1001,1,'smtp','adfg-fgbh','10/01/11' UNION
SELECT 1002,1,'odbc','erth-bgfh','10/01/11' UNION
SELECT 1003,1,'html','rfgt-dsww','10/01/11' UNION
SELECT 1004,2,'word','edfg-tghj','05/01/12' UNION
SELECT 1005,2,'smtp','wwsk-plon','05/01/12' UNION
SELECT 1006,3,'smtp','fghg-gfde','10/11/12'

SELECT * FROM SERVERLICENSE
SELECT * FROM TOOLLICENSE

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources