Not getting expected results from query???

Question

Post reply

Not getting expected results from query???

Ben Blackham

SSC Enthusiast

Points: 110
More actions
January 10, 2006 at 10:34 am

#166681

Hello all,
How are ya?
If anyone could help with the following I'd appreciate it...
I've got 2 tables
Table1 and Table2
Table 1 has 2.7M records and Table2 has 1.7M
When I try to set a matched column in table 1 to match all records between the two files that have the same 'number' column I get an unexpected result.
This is the SQL I'm using
UPDATE Table1 SET match = 'match'
FROM Table1, Table2
WHERE Table1.number = Table2.number
When I run the query it says its affected over 2M records yet there are only 1.7 records in table 2 to match against.
This doersn't make sense to my simple mind
Can anyone see what I might be doing wrong (I am a novice).
Thanks
Ben

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Is the number column unique in Table1 ? Unique in Table2 ?

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

You probably have multiple rows in table1 that match a given row in table2.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Ben Blackham SSC Enthusiast Points: 110 More actions · Answer 3

Thanks both,

Yep, you're right!

The column I was using insn't unqiue.

Thanks very much,

Do you know if, with SQL Server, it is possible to run queries accross databases from different servers?

I have serveral servers registered in the same Enterprise manager

and I want to run a query like:

select *

from ,

where ....

I can't seem to get the syntax and I'm suspecting its not possible.

tahnks

Ben

Michael Carey-400169 Mr or Mrs. 500 Points: 512 More actions · Answer 4

To run a query across databases from different servers registered in the same Enterprise Manager, just do:

Select WhateverTable.SomeField

From

OtherDatabase..WhateverTable

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

Hmmm, does that work across servers? I would think you would have to create a linked server to accomplish that.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Henry Li SSC Veteran Points: 243 More actions · Answer 6

Hello All,

I am a new user of SQL Enterprise, could anybody tell me where to run a SQL command in Enterprise? (not in the Query Designer)

I want to create a table from some query results like this:

Create table A as

Select *, field1*10, field2*5

from OldTable1

Thanks a lot!

Henry

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 7

Maybe you are trying to find Query Analyzer? You can find it in the SQL Server group in Programs or in Enterprise Manager by looking in the Tools menu.

In Query Analyzer, you must type the statement in the window and you can run it. It is not a query builder.

The command you are looking for is

Select *, field1 * 10 as Field1Times10, field2 * 5 as Field2Times5
INTO A
from OldTable1

This command will create a table called A. Note that you must give your formula columns each a name.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor