Update SQL

• MyTSQLRepl

  SSC Eights!

  Points: 824

  More actions

  April 7, 2015 at 9:16 am

  #306467

  Hello All,

  I have to build conditional update in SQL script. Can someone please help me atlest to start of the query?

  Here is the criteria:

  1. If tbl1 has no record with Batch = NULL and tbl1 and tbl2 have perfect match for Identifier-Container, Batch-Lot and Material-FromValue, get all the matched records from tbl2. Update StatusID = 1

  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

  3. If tbl1 has more than one Batch = NULL records for Identifier, Batch and Material combination, update StatusID = 7 (error)

  4. If tbl1 has atleast one Batch = NULL but rest of the records match with tbl2, update StatusID = 8 (error)

  5. If tbl1 has two records with Batch = NULL, but Material is different then update StatusID = 3 (example 'Del006')

  In example 'Del005', these two records are bad as per #4

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del005', 'B5-1', 'M5-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del005', NULL, 'M5-1')

  But this record is good as per 2#

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del005', NULL, 'M5-2')

  SQL -

  create table #tbl1 (Identifier varchar(10), Batch varchar(10), Material varchar(10), StatusID INT)

  create table #tbl2 (Container varchar(10), Lot varchar(10), FromValue varchar(10))

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del001', 'B1-1', 'M1-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del001', 'B1-1', 'M1-2')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del002', NULL, 'M2-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del003', 'B3-1', 'M3-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del004', NULL, 'M4-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del004', NULL, 'M4-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del005', 'B5-1', 'M5-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del005', NULL, 'M5-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del005', NULL, 'M5-2')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del006', NULL, 'M6-1')

  insert into #tbl1 (Identifier, Batch, Material) VALUES('Del006', NULL, 'M6-2')

  insert into #tbl2 VALUES ('Del001', 'B1-1', 'M1-1')

  insert into #tbl2 VALUES ('Del001', 'B1-1', 'M1-2')

  insert into #tbl2 VALUES ('Del002', 'B2-1', 'M2-1')

  insert into #tbl2 VALUES ('Del002', 'B2-2', 'M2-1')

  insert into #tbl2 VALUES ('Del003', 'B3-1', 'M3-1')

  insert into #tbl2 VALUES ('Del003', 'B3-1', 'M3-1')

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

  insert into #tbl2 VALUES ('Del006', 'B6-1', 'M6-2')

  Thanks,