Quick help needed with simple update query

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

Transact-SQL (2005)

Quick help needed with simple update query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-09-06 : 08:35:09

What's wrong with this query?

Update contact
Set contact.direct_line = contact.phone
where contact.phone != company.phone
left join Company on contact.company_id = company.company_id

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-09-06 : 10:02:10

[code]Update contact
Set contact.direct_line = contact.phone
FROM contact
LEFT JOIN Company on contact.company_id = company.company_id
where contact.phone != company.phone
[/code]

Mark

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 10:12:07

FWIW We use mwjdavidson's format, but use an ALIAS of "U" to clearly show which table is being updated, so for us it would be:


Update U
Set direct_line = contact.phone
FROM contact AS U
LEFT JOIN Company on U.company_id = company.company_id
where U.phone != company.phone

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 10:16:46

quote:
Originally posted by Kristen

FWIW We use mwjdavidson's format, but use an ALIAS of "U" to clearly show which table is being updated, so for us it would be:

Update U
Set direct_line = contact.phone
FROM contact AS U
LEFT JOIN Company on U.company_id = company.company_id
where U.phone != company.phone

Kristen

Exactly what I prefer to use

Madhivanan

Failing to plan is Planning to fail

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-09-06 : 11:24:47

Personally, I always alias these too, but not necessarily with a 'U'. Not a bad idea though!

Mark

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 11:30:42

Ditto D for DELETE

DELETE D
FROM MyTable1 AS D
JOIN MyTable2 AS XXX
ON XXX.ID = D.ID
WHERE XXX.Col1 = 'FooBar'

that type of thing.

I also use S = Source and D=Destination

INSERT INTO MyTable
SELECT S.*
FROM RemoteServer.RemoteDatabase.dbo.MyTable AS S
JOIN MyTable AS D
ON D.ID = S.ID
WHERE D.ID IS NULL

Usually these take the form of a "Delete if no longer in OtherDatabase", "Update if in both databases [and different]", "Insert in NOT in TARGET database", and I use S and D aliases consistently as the Source/Destination

Luckily during the DELETE step the Destination database is also the Delete-from database, so the meaning of "D" is consistent! but the UPDATE U logic changes to "UPDATE D" ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 12:13:45

<<
I also use S = Source and D=Destination
>>

I use S = Source and T=Target

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources