asked on

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.

Ryan Chong

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

Dustin Saunders

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).

Mark Wills

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 :)

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Thanks Scott