updating a table with othertable status

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 2000 Forums

SQL Server Development (2000)

updating a table with othertable status

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-11 : 14:24:56

i have table1 which has : id,flag1,flag2
and another table2 which has : id,status
the status contains the values 1 and 2
i want to update table1 where flag1 will get the value 1 if the status is 1 (and if there is no status for that id it wil stay null)
and for flag2 it will recive value 2 (and if there is no status for that id it wil stay null)

how do do that?
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-11 : 14:29:18

Could you post sample data of what it will look like before the update and sample data of what it will look like after the update? It is very hard to understand what you want without an example.

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-11 : 14:37:44

Is this what you are looking for?


USE Northwind
GO

CREATE TABLE myTable99 ([id] int, flag1 int, flag2 int)
CREATE TABLE myTable00 ([id] int, status int)
GO

INSERT INTO myTable99([id])
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

INSERT INTO myTable00([id], status)
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,1 UNION ALL SELECT 3,2
GO

SELECT * FROM myTable00
SELECT * FROM myTable99
GO

UPDATE a SET flag1 = status 
  FROM myTable99 a 
  JOIN myTable00 b ON a.[id] = b.[id]
 WHERE status = 1

UPDATE a SET flag2 = status 
  FROM myTable99 a 
  JOIN myTable00 b ON a.[id] = b.[id]
 WHERE status = 2
GO

SELECT * FROM myTable00
SELECT * FROM myTable99


DROP TABLE myTable00, myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-11 : 14:42:27

thnaks alot!
but can i combine the 2 updates into 1?
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 15:10:37

You mean something like this?

UPDATE		a
SET		a.flag1 = CASE WHEN b.Status = 1 THEN 1 ELSE a.flag1 END,
		a.flag2 = CASE WHEN b.Status = 2 THEN 2 ELSE a.flag2 END
FROM		myTable99 a 
INNER JOIN	myTable00 b ON a.[id] = b.[id]

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-11 : 15:35:51

yep thanks alot
tried to do it liek that but now see the nistake
thnaks

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources