just one more condition!!! - SQL Server Forums

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

just one more condition!!!

Author

Topic

Indyyan
Starting Member

31 Posts

Posted - 2004-07-15 : 11:24:39

Hello again!!!

As you can see in one of my former posts, I use this query to
update / insert the PK in my table, like this:

[Code]
update dbo.Best
set dbo.Best.Stålkod = dbo.Temp1.Stålkod,
dbo.Best.Slabs_Bredd = dbo.Temp1.Slabs_Bredd
from Best join dbo.temp1 on Best.Best_Nr = temp1.Best_Nr
where dbo.Best.Best_Nr = dbo.Temp1.Best_Nr **
insert into dbo.Best
(.......)
select
........ from dbo.Temp1
where dbo.Temp1.Best_Nr not in (select Best_Nr from dbo.Best)
[/Code]

This query works wonderful... but for the import of the new table, I need a little bit more to get statisfying result.
Fact is that the PK returns after 4 years. And therefore i need a condition like **`"and dbo.Best.createddate = dbo.Temp1.createddate" (look up to the two ** in the code)
Unfortunately this condition is not enough.

This query would just update the row with the same PK and createddate but wouldn´t insert the row with the same PK and the different createddate.
Do someone have a sollution for this problem....???

Thanks

Jan

Indyyan
Starting Member

31 Posts

Posted - 2004-07-15 : 12:45:27

..I looked a little bit in the Internet and found a hint...
... [Code]
update dbo.Best
set dbo.Best.Stålkod = dbo.Temp1.Stålkod,
dbo.Best.Slabs_Bredd = dbo.Temp1.Slabs_Bredd
from Best join dbo.temp1 on Best.Best_Nr = temp1.Best_Nr
where dbo.Best.Best_Nr = dbo.Temp1.Best_Nr **
insert into dbo.Best
(.......)
select
........ from dbo.Temp1
LEFT OUTER JOIN dbo.Best
ON dbo.temp1.Best_Nr = dbo.Best.Best_Nr
AND dbo.temp1.SkapadDat = dbo.Best.SkapadDat
WHERE dbo.Best.Best_Nr IS NULL
[/Code]

But there is still a problem... i get this failure message:
Server: Msg 209, Level 16, State 1, Line 46
Ambiguous column name '.....'

Can someone help me???

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-16 : 02:56:41

If the column names in both the source and joined table are the same then, at any place where you use them in your query, you must prefix the column name with the table or alias name that you are using.

EG SELECT first.moo from first inner join second on first.moo = second.moo

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources