update old data with new data

Question

update old data with new data

pk2dpvp

Mr or Mrs. 500

Points: 537
More actions
June 5, 2014 at 6:50 am

#292139

I got this script but all I get are the errors:
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
script:
UPDATE artikelen
SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,
verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,
gewijzigd = getDate()
WHERE(PartNrFabrikant in(
SELECT PartNrFabrikant, omschrijving, verkoopprijs
FROM artikelen AR
LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP
ON AR.omschrijving != SP.omschrijving
OR
(AR.verkoopprijs != SP.verkoopprijs) ))

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

The first 3 errors are there because those columns might be present in multiple tables, and SQL Server doesn't know from which table to take it.

The last error is because you did the following:

colA IN (SELECT colA, colB, colC FROM ...)

This doesn't work. You are trying to compare one column with 3 columns at once.

Rewrite to

colA IN (SELECT colA FROM ...)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pk2dpvp Mr or Mrs. 500 Points: 537 More actions · Answer 2

okay so I rewrote the columns, as you said...

UPDATE artikelen

SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,

verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,

gewijzigd = getDate()

WHERE(PartNrFabrikant in(

SELECT ar.PartNrFabrikant, ar.omschrijving, ar.verkoopprijs

FROM artikelen AR

LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP

ON AR.omschrijving != SP.omschrijving

OR

(AR.verkoopprijs != SP.verkoopprijs) ))

the last error is still there though...

Msg 116, Level 16, State 1, Line 13

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

You tried to explain to me why that happened, though I am unable to understand it :p

sorry I'm pretty new to sql server

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Here you go:

UPDATE artikelen

SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,

verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,

gewijzigd = getDate()

WHERE(PartNrFabrikant in(

SELECT ar.PartNrFabrikant -- only 1 column here

FROM artikelen AR

LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP

ON AR.omschrijving != SP.omschrijving

OR

(AR.verkoopprijs != SP.verkoopprijs) ))

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pk2dpvp Mr or Mrs. 500 Points: 537 More actions · Answer 4

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "Hofstede.dbo.sparepartsupdate.omschrijving" could not be bound.

Msg 4104, Level 16, State 1, Line 3

The multi-part identifier "Hofstede.dbo.sparepartsupdate.verkoopprijs" could not be bound.

.....

:p

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

UPDATE a

SET omschrijving= SP.[omschrijving]

,verkoopprijs= SP.[verkoopprijs]

,gewijzigd= getDate()

FROM artikelen a

LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP

ON a.PartNrFabrikant = SP.PartNrFabrikant

WHERE (AR.omschrijving != SP.omschrijving) OR (AR.verkoopprijs != SP.verkoopprijs);

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pk2dpvp Mr or Mrs. 500 Points: 537 More actions · Answer 6

very nice, thanks alot! 😀

I've been struggling with this for a few hours...

Thanks for the help! really appreciated 😀

groetjes :p

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

pk2dpvp (6/5/2014)
very nice, thanks alot! 😀
I've been struggling with this for a few hours...
Thanks for the help! really appreciated 😀
groetjes :p

De groeten terug 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pk2dpvp Mr or Mrs. 500 Points: 537 More actions · Answer 8

Koen Verbeeck (6/5/2014)
UPDATE a SET omschrijving= SP.[omschrijving] ,verkoopprijs= SP.[verkoopprijs] ,gewijzigd= getDate() FROM artikelen a LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP ON a.PartNrFabrikant = SP.PartNrFabrikant WHERE (!!!A.omschrijving != SP.omschrijving) OR (!!!A.verkoopprijs != SP.verkoopprijs);