[Resolved] Update table w. data from another table

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

General SQL Server Forums

New to SQL Server Programming

[Resolved] Update table w. data from another table

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-28 : 11:07:32

I have a sp where I have 2 tables. I have already populated ecah table with data.

Now I need to update Table1 with data from Table2.

Key fields between the 2 tables are job_date, job_number and job_phase.

If the record exists in Table1 (reading Table2) need to update record in Table1 with qty_received from Table2.

If record does not exists in Table1, need to insert record into Table1 with job_date, job_number, job_phase and qty_received from Table2

I am very new to sql and wonder if someone would be so kind to help me out? Thank you.

Table1 ie Target
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(8,2)
qty_received decimal (8,2)

Table2 ie Source
job_date datetime,
job_number char(15),
job_phase char(15),
qty_received decimal(8,2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:14:54

Are you using SQL Server 2008? Use the new MERGE command.

Otherwise, do an UPSERT.

-- Step 1, the UPdate part
UPDATE t1
SET t1.* = t2.*
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.Job_Date = t1.Job_Date AND t2.Job_Number = t1.Job_Number AND t2.Job_Phase = t1.Job_Phase

-- Step 2, the inSERT part
INSERT Table1 (col list here...)
SELECT t2.*
FROM Table2
WHERE NOT EXIST (SELECT * FROM Table1 AS t1 WHERE t2.Job_Date = t1.Job_Date AND t2.Job_Number = t1.Job_Number AND t2.Job_Phase = t1.Job_Phase)

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-28 : 11:38:55

Using Sequel Server 2000. By the way, I like the 'UPSERT' word...

Get following syntax errors:
Incorrect syntax near '*'
Incorrect syntax near keyword 'Select'
Incorrect syntax near ')'

CREATE TABLE	Table1
		(
			job_date datetime,
			job_number char(15),
			job_phase char(15),
			qty_delivered decimal(8,2),
			qty_received decimal(8,2),
                        plant_id char(10)
                                        		)

CREATE TABLE	Table2
		(
			job_date datetime,
			job_number char(15),
			job_phase char(15),
			qty_received decimal(8,2),
			plant_id char(10)
                                        		)

-- Step 1, the UPdate part
UPDATE t1
SET t1.* = t2.*
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.Job_Date = t1.Job_Date AND t2.Job_Number = t1.Job_Number AND t2.Job_Phase = t1.Job_Phase

-- Step 2, the inSERT part
INSERT Table1 (job_date, job_number, job_phase, qty_received)
SELECT t2.*
FROM Table2
WHERE NOT EXIST (SELECT * FROM Table1 AS t1 WHERE t2.Job_Date = t1.Job_Date AND t2.Job_Number = t1.Job_Number AND t2.Job_Phase = t1.Job_Phase)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:40:32

I made a shortcut when writing the * as column name. I meant for you to replace it with the actual column names.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:43:34

[code]CREATE TABLE Table1
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(8,2),
qty_received decimal(8,2),
plant_id char(10)
)

CREATE TABLE Table2
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_received decimal(8,2),
plant_id char(10)
)

-- Step 1, the UPdate part
UPDATE t1
SET t1.qty_delivered = t2.qty_delivered,
t1.qty_received = t2.qty_received,
t1.plant_id = t2.plant_id
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.Job_Date = t1.Job_Date
AND t2.Job_Number = t1.Job_Number
AND t2.Job_Phase = t1.Job_Phase

-- Step 2, the inSERT part
INSERT Table1
(
job_date,
job_number,
job_phase,
qty_delivered,
qty_received,
plant_id
)
SELECT t2.job_date,
t2.job_number,
t2.job_phase,
t2.qty_delivered,
t2.qty_received,
t2.plant_id
FROM Table2 AS t2
LEFT JOIN Table1 AS t1 ON t1.Job_Date = t2.Job_Date
AND t1.Job_Number = t2.Job_Number
AND t1.Job_Phase = t2.Job_Phase
WHERE t1.Job_Date IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:46:05

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=upsert

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-29 : 09:37:15

Still having some issues. When I run the program calling the sp I get following errors:

Column prefix '#t2' does not match with a table name or alias name used in the query.

I get error listed 7 times.

CREATE TABLE	#t1
		(
			job_date datetime,
			job_number char(15),
			job_phase char(15),
			qty_delivered decimal(8,2),
			qty_received decimal(8,2),
                        plant_id char(10)
                                        		)

CREATE TABLE	#t2
		(
			job_date datetime,
			job_number char(15),
			job_phase char(15),
			qty_received decimal(8,2),
			plant_id char(10)
                                        		)

-- Step 1, the UPdate part
UPDATE		#t1
SET		#t1.qty_received = #t2.qty_received,
		#t1.plant_id = #t2.plant_id
FROM		#t1 AS t1
INNER JOIN	#t2 AS t2 ON t2.job_date = t1.job_date
			AND t2.job_number = t1.job_number
			AND t2.job_phase = t1.job_phase

-- Step 2, the inSERT part
INSERT		#t1
		(
			job_date,
			job_number,
			job_phase,
			qty_received,
			plant_id
		)
SELECT	#t2.job_date,
		#t2.job_number,
		#t2.job_phase,
		#t2.qty_received,
		#t2.plant_id
FROM		#t2 AS t2
LEFT JOIN	#t1 AS t1 ON t1.job_date = t2.job_date
			AND t1.job_number = t2.job_number
			AND t1.job_phase = t2.job_phase
WHERE		t1.job_date IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 09:41:27

[code]CREATE TABLE #t1
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(8,2),
qty_received decimal(8,2),
plant_id char(10)
)

CREATE TABLE #t2
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_received decimal(8,2),
plant_id char(10)
)

-- Step 1, the UPdate part
UPDATE t1
SET t1.qty_received = t2.qty_received,
t1.plant_id = t2.plant_id
FROM #t1 AS t1
INNER JOIN #t2 AS t2 ON t2.job_date = t1.job_date
AND t2.job_number = t1.job_number
AND t2.job_phase = t1.job_phase

-- Step 2, the inSERT part
INSERT #t1
(
job_date,
job_number,
job_phase,
qty_received,
plant_id
)
SELECT t2.job_date,
t2.job_number,
t2.job_phase,
t2.qty_received,
t2.plant_id
FROM #t2 AS t2
LEFT JOIN #t1 AS t1 ON t1.job_date = t2.job_date
AND t1.job_number = t2.job_number
AND t1.job_phase = t2.job_phase
WHERE t1.job_date IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-29 : 10:34:42

Purrrfect, now working..... thank you (or tack sa mycket)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources