SQL Update Query Question

I am fairly new to SQL Server and using SQL 14 Developer.  I know what I want to accomplish but I'm not sure how to accomplish it.

This involves 2 table, 'tblProperty' and 'SQHD_PropHeader_Work'.

The pertinent field in 'tblProperty' are
Muni                             int
LotBlock                       nvarchar(17)
OldControlNumber     int

in 'SQHD_PropHeader_Work'.
MuniCode                  small int
LotBlock                      nvarchar(17)
ControlNumber         int

The combination of Muni and LotBlock is Unique in both tables

For every MuniCode, Lotblock combination that exists is tblProperty  a select statement to join them would look like this:
SELECT        dbo.SQHD_PropHeader_Work.MuniCode, dbo.SQHD_PropHeader_Work.LotBlock, dbo.SQHD_PropHeader_Work.ControlNumber, 
                         dbo.tblProperty.OldControlNumber
FROM            dbo.SQHD_PropHeader_Work LEFT OUTER JOIN
                         dbo.tblProperty ON dbo.SQHD_PropHeader_Work.MuniCode = dbo.tblProperty.Muni AND dbo.SQHD_PropHeader_Work.LotBlock = dbo.tblProperty.LotBlock

Open in new window



I want to update the 'OldControlNumber' field in 'tblProperty' with the corresponding 'ControlNumber' field in  'SQHD_PropHeader_Work

I don't know how to create the update query to accomplish this.  Is it possible.
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
try this:

declare @tblProperty table
(
	Muni int,
	LotBlock nvarchar(17),
	OldControlNumber int
)

declare @SQHD_PropHeader_Work table
(
	MuniCode smallint,
	LotBlock nvarchar(17),
	ControlNumber int
)

insert into @tblProperty
values
(1, 'A', 0),
(2, 'A', 0),
(1, 'B', 0),
(1, 'C', 0),
(1, 'D', 0);

insert into @SQHD_PropHeader_Work
values
(1, 'A', 10),
(2, 'A', 20),
(1, 'B', 30),
(1, 'C', 40),
(1, 'E', 50);

Select * from @tblProperty

Update a set a.OldControlNumber = b.ControlNumber
from @tblProperty a inner join @SQHD_PropHeader_Work b
on a.Muni = b.MuniCode and a.LotBlock = b.LotBlock

Select * from @tblProperty

Open in new window

0
Dustin SaundersDirector of OperationsCommented:
UPDATE tblProperty
SET tblProperty.OldControlNumber = SQHD_PropHeader.ControlNumber
FROM            dbo.SQHD_PropHeader_Work LEFT OUTER JOIN
                         dbo.tblProperty ON dbo.SQHD_PropHeader_Work.MuniCode = dbo.tblProperty.Muni AND dbo.SQHD_PropHeader_Work.LotBlock = dbo.tblProperty.LotBlock

Open in new window


You can do an update on a join and just set the value (see above).
0
Mark WillsTopic AdvisorCommented:
Would prefer to see an inner join to make sure of a match (or not joined).

While both Ryan and Dustin have answers, I think Ryan has followed the same concern by using an inner join.

Ryan updates the table (well, using table variables to exemplify) using the alias which is perfectly legitimate in SQL. Dustin is updating using actual table names....

Both are good answers :)
0
Scott PletcherSenior DBACommented:
UPDATE P
SET OldControlNumber = SPW.ControlNumber
FROM dbo.SQHD_PropHeader_Work SPW INNER JOIN
    dbo.tblProperty P ON SPW.MuniCode = P.Muni AND SPW.LotBlock = P.LotBlock
WHERE P.OldControlNumber <> SPW.ControlNumber


ALWAYS use an alias to UPDATE using a JOIN, ALWAYS.  Otherwise it's very easy to UPDATE all rows and/or the wrong rows.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks Scott
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.