SQLTeam.com | Weblogs | Forums

Conditional Insert Statement


#1

Afternoon all,

Basically I'm try to combine data from a temp table. Let me try to explain in sudo code...

IF
temptable.roadname = table.roadname
AND
temptable.postcode = table.postcode
AND
temptable.name IS empty / NULL / nothing
THEN
table.name = temptable.name

Temptable
Name Roadname Postcode
Bob 1st Ave AA1 1AA
Dan 2nd Ave BB1 1BB
Phil 3rd Ave CC1 1CC
Gemma 4th Ave DD1 1DD
Claire 5th Ave EE1 1EE
Sarah 6th Ave FF1 1FF
Ren 7th Ave GG1 1GG

Table
Name Roadname Postcode
Alex 1st Ave AA1 1AA
2nd Ave BB1 1BB
Phil 3rd Ave CC1 1CC
8th Ave DD1 1DD
Claire 5th Ave EE1 1EE
6th Ave HH2 2HH
Ren 7th Ave GG1 1GG

Expected Result
Table
Name Roadname Postcode
Alex 1st Ave AA1 1AA
Dan 2nd Ave BB1 1BB
Phil 3rd Ave CC1 1CC
8th Ave DD1 1DD
Claire 5th Ave EE1 1EE
6th Ave HH2 2HH
Ren 7th Ave GG1 1GG

SQL Wise I've currently got

INSERT INTO table (Name)
SELECT Name
FROM tamptable$
WHERE temptable$.name = table.name

But it doesn't like it.....
Any pointers would be greatly apreciated.

Thanks

Dave


#2
UPDATE t SET
	t.NAME = tt.NAME
FROM
	[Table] AS t
	INNER JOIN TempTable AS tt ON
		t.roadname = tt.roadname
		AND t.postcod = tt.postcode
		AND (tt.NAME IS NULL OR tt.NAME = '')

#3

Hi James,

Perfect, thank you :smiley: