Tricky Update - 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 2012 Forums

Transact-SQL (2012)

Tricky Update

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-04-06 : 16:06:31

I have two tables tbl1 and tbl2 and the match between the two is-
RecordIdentifier, Batch# and Component#.

There can be more than 1 line items (Batch# and Component#) for a RecordIdentifier.

I have to update the tbl1 with different statuses on following scenarios -

1. For single/more line item records,
if tbl1.RecordIdentifier = tbl2.RecordIdentifier, tbl1.Batch = tbl2.Batch and tbl1.Component = tbl2.Component
then it's perfect match and update tbl1.status = Good
But if tbl2 brings more data for same keys, tbl1.status = Bad

2. If one line item for a RecordIdentifier,
tbl1.Batch is NULL then tbl1.Status = Bad

3. For two/more line item records,
if tbl1.RecordIdentifier = tbl2.RecordIdentifier BUT tbl1.Batch is NULL on any of the line item for same tbl1.Component and tbl12.component then tbl1.status = Bad. If tbl1.component <> tbl2.component then only that line item is tbl1.status = Partial.

4. At any point tbl2 table brings back more records for these three column combinations then tbl1.status = Bad.

Here is the example:
tbl1-
RecordIdentifier Batch Component Status
Del001 B1-1 M1-1 1
Del001 B1-2 M1-1 1
Del003 B3-1 M3-1 3
Del003 NULL M3-1 3
Del003 NULL M3-2 2
Del004 B4-1 M4-1 3
Del005 B5-1 M5-1 3
Del005 NULL M5-1 3
Del006 NULL M6-1 3
Del007 B7-1 M7-1 3
Del009 B9-1 M9-1 3
Del009 B9-2 M9-1 3
Del010 B10-1 M10-1 3
Del010 B10-2 M10-1 3
Del011 NULL M11-1 1

tbl2
RecordIdentifier Batch Component
Del001 B1-1 M1-1
Del001 B1-2 M1-1
Del003 B3-1 M3-1
Del003 B3-2 M3-1
Del003 B3-3 M3-2
Del004 B4-1 M4-1
Del004 B4-2 M4-1
Del005 B5-1 M5-1
Del005 B5-2 M5-1
Del006 B6-1 M6-1
Del007 B7-1 M7-1
Del007 B7-1 M7-2
Del009 B9-1 M9-1
Del010 B10-1 M10-1
Del010 B10-2 M10-2
Del011 B11-1 M11-1

Status Good(1), Bad (3), Partial(2)

CREATE TABLE tbl1 (RecordIdentifier varchar(100), Batch varchar(100), Material varchar(100), Status INT)
CREATE TABLE tbl2 (RecordIdentifier varchar(100), Batch varchar(100), Material varchar(100))

Can someone please kindly help me with the update query here?

Appreciate your help.

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-04-06 : 20:05:13

Hello, Can somebody please help me to begin the update script?

Thanks,

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2015-04-06 : 20:07:21

Here is the example of insert to tbl1 table as the formatting got messed up in the question.
insert into tbl1 (RecordIdentifier, Batch, Material) values ('Del001', 'B1-1', 'M1-1')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources