[SOLVED] Update table based on another table help

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

General SQL Server Forums

New to SQL Server Programming

[SOLVED] Update table based on another table help

Author

Topic

cal1972
Starting Member

4 Posts

Posted - 2011-10-05 : 07:46:31

I'm struggling with a SQL script that I think is probably really basic but I'm new to this.

Table 1
LOCID
LOCCODE

Table 2
LOCID
FLAG

I'm trying to update Table 2 and set FLAG=1 where the LOCID exists in Table 1.

Here is what I have so far but it is updating everything:

UPDATE dbo.TABLE1
set FLAG = 1
WHERE EXISTS (SELECT * from dbo.TABLE1, dbo.TABLE2
WHERE dbo.TABLE1.LOCID = dbo.TABLE2.LOCID)

Any ideas? thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 07:48:56

[code]
UPDATE t2
set t2.FLAG = 1
FROM dbo.TABLE1 t1
JOIN dbo.TABLE2 t2
ON t2.LOCID = t1.LOCID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cal1972
Starting Member

4 Posts

Posted - 2011-10-05 : 07:55:21

Thanks, I'm still getting JOINs.

quote:
Originally posted by visakh16

UPDATE t2
set t2.FLAG = 1
FROM dbo.TABLE1 t1
JOIN dbo.TABLE2 t2
ON t2.LOCID = t1.LOCID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 07:59:16

hmm..what does that mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-05 : 08:57:38

Hi,

Try using UPDATE TABLENAME SET COLUMNNAME FROM TABLENAME INNER JOIN TABLENAME2 ON TABLENAME.LOCID=TABLENAME2.LOCID

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-05 : 09:12:54

quote:
Originally posted by cal1972

I'm struggling with a SQL script that I think is probably really basic but I'm new to this.

Table 1
LOCID
LOCCODE

Table 2
LOCID
FLAG

I'm trying to update Table 2 and set FLAG=1 where the LOCID exists in Table 1.

Here is what I have so far but it is updating everything:

UPDATE dbo.TABLE1
set FLAG = 1
WHERE EXISTS (SELECT * from dbo.TABLE1, dbo.TABLE2
WHERE dbo.TABLE1.LOCID = dbo.TABLE2.LOCID)

Any ideas? thanks in advance!

This?

update t2
set FLAG=1
from table2 as t2
where exists(select * from table1 as t1 where t1.LOCID = t2.LOCID)

No, you're never too old to Yak'n'Roll if you're too young to die.

cal1972
Starting Member

4 Posts

Posted - 2011-10-05 : 09:17:32

Sorry. The first response fixed the issue. I just meant i'm still learning to understand JOIN's.

Thanks again

quote:
Originally posted by cal1972

Thanks, I'm still getting JOINs.

quote:
Originally posted by visakh16

UPDATE t2
set t2.FLAG = 1
FROM dbo.TABLE1 t1
JOIN dbo.TABLE2 t2
ON t2.LOCID = t1.LOCID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cal1972
Starting Member

4 Posts

Posted - 2011-10-05 : 09:31:47

How do i mark it as accepted. I'm overlooking something.

quote:
Originally posted by jassi.singh

Hi,

Try using UPDATE TABLENAME SET COLUMNNAME FROM TABLENAME INNER JOIN TABLENAME2 ON TABLENAME.LOCID=TABLENAME2.LOCID

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-05 : 10:12:31

On the overview page you - as the author - should have an "edit topic"-button to the right side.
So you can edit the topic as "[solved] Update table based on another table help" for example.

But the most question-posting-people don't do that here...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources