Update statement problem

Question

Update statement problem

Don Adams-248152

Newbie

Points: 8
More actions
August 27, 2007 at 5:45 am

#180416

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

Viewing 13 posts - 1 through 12 (of 12 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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&gt 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?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

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]

SQL-4-Life

Don Adams-248152 Newbie Points: 8 More actions · Answer 3

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

roger price-482298 SSC Veteran Points: 206 More actions · Answer 4

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> ))

Don Adams-248152 Newbie Points: 8 More actions · Answer 5

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

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]

SQL-4-Life

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

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?

Don Adams-248152 Newbie Points: 8 More actions · Answer 8

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

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]

SQL-4-Life

Don Adams-248152 Newbie Points: 8 More actions · Answer 10

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

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?

Don Adams-248152 Newbie Points: 8 More actions · Answer 12

Thanks! I got it worked out now. I agree, it's a pain the way I was trying to do this.

Don