Update table and join another - 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 table and join another

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-05-14 : 21:38:46

TABLE1 has 1000 records.
I created TMP_TABLE for the records (20 records)

I wanted to update only the 10 records in TABLE1
where they are found in TMP_TABLE.

I did the following

UPDATE DBO.TABLE1
SET dbo.TABLE1.ColC = 'F'
FROM dbo.TMP_TABLE
INNER JOIN dbo.TABLE1
ON (dbo.TMP_TABLE.ColA = dbo.TABLE1.ColA);

It updated more than 20 records on TABLE1 i was expecting only the 20 to be updated in TABLE1.

So i had to put another where selection ...
WHERE TMP_TABLE.COLC = 'S,'P'

Basically TMP_TABLE has 'S','P' and im updating them all to 'F'
The TMP_TABLE is built by another three or four tables.

I dont quite understand why i had to include the WHERE clause.

Any suggestions.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 22:05:16

quote:
I did the following

UPDATE DBO.TABLE1
SET dbo.TABLE1.ColC = 'F'
FROM dbo.TMP_TABLE
INNER JOIN dbo.TABLE1
ON (dbo.TMP_TABLE.ColA = dbo.TABLE1.ColA);

It updated more than 20 records on TABLE1 i was expecting only the 20 to be updated in TABLE1.

try this and see


select *
from   TABLE1
where  ColA in (select ColA from TMP_TABLE)

and also

select count(distinct ColA)
from   TMP_TABLE

if Query 1 gives you > 20 recs basically this means you have more then one row with same value of ColA in TABLE

if Queyr 2 does not gives you 20, it means you have more then one row with same value of ColA in TMP_TABLE

KH
[spoiler]Time is always against us[/spoiler]

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-05-15 : 16:41:20

Thanks i got it working

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-05-15 : 17:28:31

sorry last thing.

If i am in table and do edit rows i get the Show SQL Pane up and Change Type.

If i just do new query and do select * from table i don't get the SHOW SQL Pane and change type.

In toolbar customise i included query designer and now when i do new query and run it i get the show sql pane and change type but these are grey out...

Are they only available when do right click table...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-16 : 03:08:11

what type are you talking about? is type a column of your table?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-05-20 : 18:33:41

I found it out....select new query do your statement and then highlight the entire thing and bring up the query designer.
Here you can then change to a UPDATE if you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources