August 27, 2007 at 5:45 am
I used to be able to do the following in SQL 2000 without any problems:
Update <table1>
Set <field1> = (Select <field2> From <table2> t2 Where t2.<PKField> = <FKField> )
Now in SQL 2005 when I check syntax the query gets rewritten automatically to:
Update <table1>
Set <field1> =
(Select <field2>
From <table2> AS t2
Where (<PKField> = <FKField> ))
When I go to execute this query I get a message that the subquery returned more than one value and no matter how I try to re-write this I get the same results.
What has happened and how can I fix it?
Thanks,
Don
August 27, 2007 at 6:52 am
In order for this to work - the sub-query really would have to return only one record matching the outer query. Have you double-checked that there are NO instances where that isn't true?
do a query like such to check:
select <fkfield>, count(field1) from table1 where fkfield in (select pkfield from <table2> having count(field1)>1
Also - any chance that <fkfield> could get confused for a field in <table2>? If that were happening - that would DEFINITELY make that error come up).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 27, 2007 at 6:53 am
Hi there,
Are you sure that the data that is being selected from now is not different?
that error normally means that you are trying to assign multiple values to a single value which is not possible!
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2007 at 7:45 am
The Where statement is based on the Primary key <PKField> and a Foreign key <FKField> combination so there can only be ONE value returned in the sub-select.
I don't understand why the 2005 version of SQL strips off my table aliases and rewrites my query. And then why it won't execute.
Is there a better way for me to write this query?
Thanks,
Don
August 27, 2007 at 8:00 am
Try selection TOP 1 of the Sub query. That will get you 1 row in the sub query.
Update <table1>
Set <field1> =
(Select TOP 1 <field2>
From <table2> AS t2
Where (<PKField> = <FKField> ))
August 27, 2007 at 8:07 am
I tried that and every record I was updating had exactly the same value. It's like it's trying to do a cross join somehow.
Don
August 27, 2007 at 8:15 am
UPDATE t1
SET t1.Field = t2.field
FROM Table1 t1
INNER JOIN Table2 t2
on t1.PKField = t2.FKField
That should work
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2007 at 8:17 am
then it's not understanding that the <fkfield> you're talking about is supposed to be coming from the outer query. force it with the table names in the sub-query. If need be, assign nicknames to the two tables:
update t1
set t1.field1=(select t2.field2 from <table2> t2 where t2.pkey=t1.fkey)
from <table1> t1
It shouldn't try to rewrite that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 27, 2007 at 8:33 am
When I try the format by Matt Miller and check syntax the query gets rewritten as follows:
UPDATE t1
SET t1.<field1> = t2.<field2>
FROM <Table1> AS t1 INNER JOIN
<Table2> AS t2 ON t1.<FKField> = t2.<PKField> CROSS JOIN
t1
And get an error message "Invalid object name 't1'".
Why would it automatically add the "CROSS JOIN t1" to my query?
Thanks,
Don
August 27, 2007 at 8:38 am
how are you creating your queries?
Can you not just type the code into the query window?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2007 at 8:42 am
I right-click on the Table1 and select "Open table" and then click the SQL button in the toolbar. I modify the query and check syntax and it rewrites my query to what it thinks I want.
Don
August 27, 2007 at 9:26 am
That's pretty buggy - I just saw the behavior you're mentioning. That's pretty sick.
The non-visual "parse" feature in the "new query" window doesn't seem to have that problem. Also - if I start the query from scratch from within the visual designer, it doesn't seem to want to insert that CROSS JOIN nonsense (which is what is causing your issue).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 27, 2007 at 9:40 am
Thanks! I got it worked out now. I agree, it's a pain the way I was trying to do this.
Don
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy