Need UPDATE/JOIN assistance. - 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 2000 Forums

SQL Server Development (2000)

Need UPDATE/JOIN assistance.

Author

Topic

Xopher
Starting Member

2 Posts

Posted - 2006-12-07 : 19:30:51

I'm working on a query (or set of queries) to separate a firstname from a fullname (table T1), and update a table in another database (T2) with the firstname. I'm using MS SQL Server. Details:

- T1 has fields fullname and lastname. I've created a query that'll pull out the firstnames:

select left(fullname, (len(fullname) - len(lastname)-1))
from T1
where (lastname <> '') and (lastname is not null)

- Both T1 and T2 have the UIDs already populated, due to an earlier step in a process I'm following. For reference, the process is here:

[url]http://bogomip.net/blog/migrating-goldmine-to-microsoft-crm/[/url]

- Thus, I want T1's firstname to dump into T2's fname, where both records' UIDs match.

I did try one UPDATE statement, but it added the names based on whether the UIDs matched, it didn't update the records where the UIDs matched, if that makes sense.

What should I be doing?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 19:49:15

[code]update q
set q.firstname = x.firstname,
q.lastname = x.lastname
from t2 AS q
inner join (
select uid,
rtrim(left(fullname, len(fullname) - len(lastname))) AS FirstName,
LastName
from T1
where ISNULL(Lastname, '') <> ''
) x on x.uid = q.uid[/code]

Peter Larsson
Helsingborg, Sweden

Xopher
Starting Member

2 Posts

Posted - 2006-12-08 : 14:09:18

That worked, Peso, thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 14:45:44

You're welcome, and good luck!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources