Updating records in table 1 based on record 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 2005 Forums

Transact-SQL (2005)

Updating records in table 1 based on record status

Author

Topic

DougR
Starting Member

6 Posts

Posted - 2009-10-01 : 18:51:10

I have 2 tables and need to update records in table 1 from table 2 based upon a status from table 2. ProductName would be updated if Status = A from table 2

Table 1:
ProductId ProductName Status
1 Name1 A
2 Name2 D
3 Name3 A

Table 2:
POverrideId ProductId ProductName Status
1 1 NewName1 A
2 2 NewName2 A
3 3 NewName4 D

Result
ProductId ProductName
1 NewName1
2 NewName2
3 Name3

I'm drawing a blank....

Thanks,

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 19:21:22

Try this DougR

UPDATE a
SET a.productname = b.productname
FROM TABLE1 a JOIN TABLE2 b
ON a.productid=b.productid
WHERE b.status = 'A'

DougR
Starting Member

6 Posts

Posted - 2009-10-01 : 19:37:08

Thanks Mivey4

That was the direction I was headed. What if Table 1 and table 2 looked this way:

Table1
ProductId ProductName PColor Status
1 Name1 C1 A
2 Name2 C2 D
3 Name3 C3 A
4 Name4 C4 A
5 Name5 C5 A

Table2
POverride ProductId ProductName PColor Status
1 1 NewName1 NULL A
2 2 NULL NewC2 A
3 3 NewName3 C3 D

Thanks

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 19:46:42

Not certain what you're expecting DougR, but the resulting logic would remain the same.

Given the new example you provided you'd get:

Result
ProductId ProductName
1 NewName1
2 NULL
3 Name3

If you don't want NULL values to be updated even if the status is "A" in table2, then you'd set a WHERE clause to omit that condition in the update.

Make sense?

DougR
Starting Member

6 Posts

Posted - 2009-10-01 : 19:57:34

That's my confusion.... Let's make it a SELECT instead...

The result should look like:
ProductId ProductName PColor Status
1 NewName1 C1 A
2 Name2 NewC2 A
3 Name3 C3 D
4 Name4 C4 A
5 Name5 C5 A

Thanks for the replies.

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 20:23:54

Don't you mean the results would be:

1 NewName1 C1 A
2 Name2 NewC2 A
3 Name3 C3 D
4 Name4 C4 A
5 Name5 C5 A

Since you initially stated that only records in table2 which have a status of A would be updated in table1. Otherwise, you've changed your own requirements.

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 20:26:11

Actually, I meant your result should be

1 NewName1 C1 A
2 Name2 NewC2 A
3 Name3 C3 A
4 Name4 C4 A
5 Name5 C5 A

DougR
Starting Member

6 Posts

Posted - 2009-10-01 : 20:37:59

Can I start over....

Table1
ProductId ProductName PColor Status
1 Name1 C1 A
2 Name2 C2 D
3 Name3 C3 A
4 Name4 C4 A
5 Name5 C5 A

Table2
POverride ProductId ProductName PColor Status
1 1 NewName1 NULL A
2 2 NULL NewC2 A
3 3 NewName3 C3 D

Need to select all records in Table1 and show the new values for Table1 based on Table2 data and Table2 Status of A only.

ProductId ProdName PColor Status
1 NewName1 C1 A
2 Name2 NewC2 A
3 Name3 C3 A
4 Name4 C4 A
5 Name5 C5 A

THANKS!

DougR
Starting Member

6 Posts

Posted - 2009-10-01 : 20:39:21

I was thinking of something like:
SELECT tblProduct.ProductId
,tblProduct.ProductName =
CASE
WHEN tblProduct.tblProductName = ISNULL(tblProductOverride.ProductName, '') THEN tblProduct.ProductName
ELSE tblProductOverride.ProductName
END
FROM tblProduct join tblProductOverride on tblProduct.ProductId = tblProductOverride.ProductId
WHERE tblProductOverride.RecordStatus='A'

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 20:52:32

You're on the right track. It's actually:

UPDATE a
SET a.productname = CASE WHEN b.productname IS NULL THEN a.productname
ELSE b.productname END,
a.PColor = CASE WHEN b.PColor IS NULL THEN a.PColor
ELSE b.PColor END,
a.status = CASE WHEN b.status IS NULL THEN a.status
ELSE b.status END
FROM TABLE1 a JOIN TABLE2 b
ON a.productid=b.productid
WHERE b.status = 'A'

Good Luck!

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 21:02:44

Oh forgot, your SELECT statement would be

SELECT CASE WHEN b.productname IS NULL THEN a.productname
ELSE b.productname END,
CASE WHEN b.PColor IS NULL THEN a.PColor
ELSE b.PColor END,
CASE WHEN b.status IS NULL THEN a.status
ELSE b.status END
FROM TABLE1 B LEFT OUTER JOIN TABLE2 A
ON a.productid=b.productid
WHERE b.status = 'A'

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 21:05:44

Or you may want to omit the WHERE clause:

SELECT CASE WHEN b.productname IS NULL THEN a.productname
ELSE b.productname END,
CASE WHEN b.PColor IS NULL THEN a.PColor
ELSE b.PColor END,
CASE WHEN b.status IS NULL THEN a.status
ELSE b.status END
FROM TABLE1 B LEFT OUTER JOIN TABLE2 A
ON a.productid=b.productid

DougR
Starting Member

6 Posts

Posted - 2009-10-01 : 23:21:54

Excellent.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources