Update without unique records - 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 2005 Forums

Transact-SQL (2005)

Update without unique records

Author

Topic

gnaus
Starting Member

41 Posts

Posted - 2011-12-29 : 06:07:07

Dear reader,

I have this formula

update dbo.[Abrona Productie$Schoenmaker2]
set MedischeInd=(select GnausConversieSchoenmakerMedInd.MedInd from GnausConversieSchoenmakerMedInd
where
Clientnr=GnausConversieSchoenmakerMedInd.Clientnr
and Bezoekdatum=GnausConversieSchoenmakerMedInd.Bezoekdatum)
where exists
(Select GnausConversieSchoenmakerMedInd.MedInd from GnausConversieSchoenmakerMedInd
where
Clientnr=GnausConversieSchoenmakerMedInd.Clientnr
and Bezoekdatum=GnausConversieSchoenmakerMedInd.Bezoekdatum)

with this error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

And it’s correct what it says because there are more records with the combination ‘Clientnr’- ‘Bezoekdatum’
In GnausSchoenmakerMedInd are the records unique, but in Abrona.Productie$choenmaker2 aren’t the records unique because there are multiple records with same combination ‘Clientnr’- ‘Bezoekdatum’.

Is there a possibility that sql can do this?
It’s not possible to change the table records! So I want to detour the problem. I Mean I want to have an other way that he just does what I want ;-)

Thank you!

GN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 07:15:46

What you want is probably something like this. Since I am not familiar with your business requirements, I am only guessing. So PLEASE test it before you use it for real.

UPDATE a SET
   MedischeInd = g.MedInd
FROM
   dbo.[Abrona Productie$Schoenmaker2] a
   INNER JOIN GnausConversieSchoenmakerMedInd g ON
      g.Clientnr = a.Clientnr
      AND g.Bezoekdatum = a.Bezoekdatum

gnaus
Starting Member

41 Posts

Posted - 2011-12-29 : 07:30:10

I get the error:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

Both tables (in Test environment) are Latin1_General_CS_AS
and the database has also Latin1_General_CS_AS

But the (production) database where it came from (I made a copy naturally, because you also said to test it first!) has SQL_Latin1_General_CP1_CI_AS

I did this before but for me it’s a big puzzel maybe you can do this in a few seconds ;-)

GN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 07:40:12

You can specify the collation like shown below (assuming the collation of the MedischeInd column in dbo.[Abrona Productie$Schoenmaker2] is Latin1_General_CI_AS

UPDATE a SET
   MedischeInd = g.MedInd COLLATE Latin1_General_CI_AS
FROM
   dbo.[Abrona Productie$Schoenmaker2] a
   INNER JOIN GnausConversieSchoenmakerMedInd g ON
      g.Clientnr COLLATE Latin1_General_CI_AS = a.Clientnr COLLATE Latin1_General_CI_AS
      AND g.Bezoekdatum COLLATE Latin1_General_CI_AS = a.Bezoekdatum COLLATE Latin1_General_CI_AS

gnaus
Starting Member

41 Posts

Posted - 2011-12-29 : 07:53:42

Sorry, ‘Bezoekdatum’ is a date..
So now I get this:
Msg 447, Level 16, State 0, Line 4
Expression type datetime is invalid for COLLATE clause.

GN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 08:05:05

Force the collation only where you need to. If Bezoekdatum is of type DATETIME, remove the collate hint from the join condition on Bezoekdatum , as in:

UPDATE a SET
   MedischeInd = g.MedInd COLLATE Latin1_General_CI_AS
FROM
   dbo.[Abrona Productie$Schoenmaker2] a
   INNER JOIN GnausConversieSchoenmakerMedInd g ON
      g.Clientnr COLLATE Latin1_General_CI_AS = a.Clientnr COLLATE Latin1_General_CI_AS
      AND g.Bezoekdatum = a.Bezoekdatum

gnaus
Starting Member

41 Posts

Posted - 2011-12-29 : 08:07:28

yes!!!! it works!!!
wow! you're good! :)
thank you very much!!!

GN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources