quote:

---

2. If tbl1 has only one record and with Batch = NULL record for Identifier, Batch and Material combination, get all tbl2 records for Identifier-Container and Material-FromValue. Update StatusID = 2

---

quote:

---

But this record is good as per 2#
insert into #tbl1 (Identifier, Batch, Material) VALUES('Del005', NULL, 'M5-2')

---

insert into #tbl2 VALUES ('Del005', 'B5-2', 'M5-2')

-- 1.
update tbl2
set statusid = 1
-- select *
from #tbl2 tbl2
join #tbl1 tbl1
  on tbl1.identifier = tbl2.container
 and tbl1.Material = tbl2.FromValue
where tbl1.batch is null and tbl2.lot is null

-- 2.
update tbl1
set statusid = statusid
--select *
from #tbl2 tbl2
join 
(
	select count(*) ct, Identifier, Batch, Material
	from #tbl1 tbl1
	group by Identifier, Batch, Material
) tbl1
 on tbl1.Identifier = tbl2.Container
and tbl1.Material = tbl2.FromValue
where tbl1.ct = 1 and tbl1.batch is null

quote:

---

Originally posted by sqlpal2007

The update for first senario doesn't return any data. Should the where clause be batch is NOT null and tbl2.lot is NOT null?

Thanks for your help

---

update tbl2
set statusid = 1
-- select *
from #tbl2 tbl2
join #tbl1 tbl1
  on tbl1.identifier = tbl2.container
 and tbl1.Material = tbl2.FromValue
 and tbl1.batch = tbl2.lot

with no_nulls as 

-- CTE to return Identifier, Material pairs in #tbl1 where Batch is never null
(
	select * 
	from
	(
		select count(*) ct, Identifier, Material
		from #tbl1 tbl1
		group by Identifier, Material
	) t1
	where not exists 
	(
		select 1 
		from #tbl1
		where Identifier = t1.Identifier
		  and Material = t1.Material
		  and batch is null
	)
),

-- CTE to add the batch back in 
no_nulls_batch as
(
	select t1.* from #tbl1 t1
	join no_nulls n
	on t1.Identifier = n.Identifier
	and t1.Material = n.Material
)

-- Actual UPDATE
update t2
set statusId = 2
from #tbl2 t2
join no_nulls_batch nn
on t2.Container = nn.Identifier
and t2.Lot = nn.Batch
and t2.FromValue = nn.Material