December 9, 2008 at 3:02 pm
Here are the tables involved:
MachineName table has two fields: machineName and machineID
MachineAudit table has two fields: machineID and leaseNumber
LeaseInfoImport table has two fields: computerName and leaseNumber
The LeaseInfoImport table was created with the DTS import wizard and the data came from an Excel sheet. Currently, the leaseNumber is not populated in the MachineAudit table. So, I am looking to populate it based on the data from the LeaseInfoImport table.
I need to do something like this, but don't know the correct syntax:
populate/update the leaseNumber row in the MachineAudit table where the LeaseInfoImport.[computerName] = MachineName.[machineName]
Thanks in advance,
Pat B.
December 9, 2008 at 3:15 pm
update a
set leasenumber = c.
from machineaudit a
inner join machinename b
on a.computername = b.machinename
inner join leaseinfoimport c
on b.leasenumber= c.leasenumber
Not sure what you want to import.
You need to check to be sure that the data is matching up correctly. I'd run this as a select first to see if things match up. Return all values and be sure of what you ware updating.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 9, 2008 at 3:16 pm
populate/update the leaseNumber row in the MachineAudit table where the LeaseInfoImport.[computerName] = MachineName.[machineName]
First...
SELECT MA.MachineID, MN.MachineName, L.LeaseNumber
FROM MachineAudit MA
INNER JOIN MachineName MN ON MA.MachineID = MN.MachineID
INNER JOIN LeaseInfoImport L ON L.[computerName] = MN.[machineName]
And if that gives you what you're looking for...
UPDATE MachineAudit
SET LeaseNumber = L.LeaseNumber
FROM MachineAudit MA
INNER JOIN MachineName MN ON MA.MachineID = MN.MachineID
INNER JOIN LeaseInfoImport L ON L.[computerName] = MN.[machineName]
December 9, 2008 at 3:21 pm
December 9, 2008 at 3:45 pm
Try this and let know this is what you require. Note that you should provide sample data and script for better response.
create table MachineName (machineName varchar(50), machineID int)
insert into MachineName values ('PC1', 1)
insert into MachineName values ('PC2', 2)
insert into MachineName values ('PC3', 3)
create table MachineAudit (machineID int, leaseNumber int)
insert into MachineAudit values (1, null)
insert into MachineAudit values (2, null)
insert into MachineAudit values (3, null)
create table LeaseInfoImport (computerName varchar(50), leaseNumber int)
insert into LeaseInfoImport values ('PC1', 11)
insert into LeaseInfoImport values ('PC2', 12)
insert into LeaseInfoImport values ('PC3', 13)
update MachineAudit set leaseNumber = b.leaseNumber
from MachineAudit a,
(select a.machineID, b.leaseNumber from MachineName a, LeaseInfoImport b
where a.machineName = b.computerName) b
where a.machineID = b.machineID
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 9, 2008 at 6:07 pm
bitbucket -
It sounds like I might have posted to the wrong forum. Is that the case? I tried to access the article you referenced (http://qa.sqlservercentral.com/articles/Best+Practices/61537/ ), but I get this error:
Sorry, an error has occurred
Please check the URL, some characters in it triggered this error.
If problems persist, please contact us to let us know.
I also just looked under the Articles section for Best Practices, but did not see it there.
What Forum should I have posted under?
Sorry for any inconvenience,
Pat
December 9, 2008 at 6:42 pm
You were fine Pat, I think he meant HE posted to the wrong forum.
The best practices post is the same one many of us (including myself) have in our signatures for how to post questions on the forum, but that's not what he was originally linking (if I remember the post from earlier correctly).
December 9, 2008 at 7:04 pm
Pbreitenbeck
I posted to the incorrect forum YOU DID NOT.
And thanks about the bug in the signature block, it used to work and I have cut and pasted the correct url into the signature block and tested and it still does not work ? ? ?
Here is the url
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
After this posts I will test again
More importantly the link in Garadin's post does work
December 9, 2008 at 7:33 pm
Worked for me.
--Jeff Moden
December 10, 2008 at 8:12 am
Thanks for all the super fast responses. Unfortunately, there are a few things that I need clarification on. This might seem basic, but it appears that you are using correlation names/table aliases in the examples (without the AS keyword), right? I thought that when using them, it is necessary to first declare them (with the AS keyword), but it appears that you are using them without declaring them. For example, in the code below MA is referenced in the SELECT statement before it is declared in the FROM statement such as MachineAudit AS MA. However, based on what I am seeing, I am gathering that it is okay to do this?
SELECT MA.MachineID, MN.MachineName, L.LeaseNumber
FROM MachineAudit MA
INNER JOIN MachineName MN ON MA.MachineID = MN.MachineID
INNER JOIN LeaseInfoImport L ON L.[computerName] = MN.[machineName]
December 10, 2008 at 8:23 am
AS is optional. I've never liked it, and never use it. I'll even go so far as to remove it in a lot of cases. (For purely aesthetic reasons)
-- EDIT --
To clarify, the following 2 lines are exactly the same:
FROM MachineAudit MA
FROM MachineAudit AS MA
December 10, 2008 at 8:48 am
I've not used AS much either.
I tend to go
SELECT MA.MachineID
, MN.MachineName
, MA.machinename 'MachineName2'
, L.LeaseNumber
FROM MachineAudit MA
INNER JOIN MachineName MN ON MA.MachineID = MN.MachineID
INNER JOIN LeaseInfoImport L ON L.[computerName] = MN.[machineName]
in the SELECT. In the FROM, I just give a short alias that makes sense, such as M, MA, etc.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 10, 2008 at 8:57 am
Egad I hate those commas on the left.:hehe:
(I know I know, it makes it easier to comment out lines and such, but I still hate it)
I do put each column in the SELECT list on a separate line if it's going to be a long list, or if I'm doing concatenation/casting etc.
December 10, 2008 at 9:47 am
SSC Veteran -
I basically understand everything in the block of code below, except the last two lines. I understand that we are first doing a SELECT query in paranthesis, but what does the last line do? The only thing I can figure is that you are re-assigning the alias of b to the resultset returned by the select query in parenthesis. So, alias b no longer refers to the LeaseInfoImport table. Is this correct?
/* last line of code*/
b where a.machineID = b.machineID
/* original code */
update MachineAudit set leaseNumber = b.leaseNumber
from MachineAudit a,
(select a.machineID, b.leaseNumber from MachineName a, LeaseInfoImport b
where a.machineName = b.computerName) b
where a.machineID = b.machineID
December 10, 2008 at 10:13 am
pbreitenbeck (12/10/2008)
/* original code */
update MachineAudit set leaseNumber = b.leaseNumber
from MachineAudit a,
(select a.machineID, b.leaseNumber from MachineName a, LeaseInfoImport b
where a.machineName = b.computerName) b
where a.machineID = b.machineID
What this is is an older style join syntax. Instead of explicitly stating the join and using the ON clause - the join is completed in the where clause. This could be rewritten as:
Update MachineAudit
Set leaseNumber = b.leaseNumber
From MachineAudit a
Inner Join (Select a.machineID
, b.leaseNumber
From MachineName a
Inner Join LeaseInfoImport b
On a.machineName = b.computerName) b
On a.machineID = b.machineID
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply