Query to check the number of rows in a table against a column in the same table

• Paarthasarathy

  SSChasing Mays

  Points: 649

  More actions

  August 19, 2009 at 8:41 am

  #225149

  I need a Query to check the number of rows in a table against a column in the same table.

  Following is the Schema,

  create table test_table

  (cnt int,

  uq_column char,

  a int,

  b char)

  insert into test_table values ( 2, 'a',1,'')

  insert into test_table values ( 2, 'a',2,'')

  insert into test_table values ( 1, 'b',10,'')

  insert into test_table values ( 3, 'c',2,'')

  insert into test_table values ( 3, 'c',1,'')

  insert into test_table values ( 3, 'c',6,'')

  insert into test_table values ( 2, 'd',6,'')

  I need to update the column b with 'p', if the number of rows against the uq_column is same as the value in the column cnt.

  That is for uq_column, we are having 2 rows and the value in the column cnt is 2. So it should be updated with p (same for b and c).

  But for the rows with uq_column as d, there is only one row. but the value in the column cnt is 2.

  It is wrong. I need to update the column for this row alone as 'F"

  Can someone suggest a query.

  Kindest Regards,
  Paarthasarathy
  Microsoft Certified Technology Specialist
  http://paarthasarathyk.blogspot.com

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  August 19, 2009 at 8:59 am

  #1041605

  Do you have any unique key for the table?

  ---------------------------------------------------------------------------------

• Paarthasarathy

  SSChasing Mays

  Points: 649

  More actions

  August 19, 2009 at 9:05 am

  #1041616

  Pakki,

  There are no unique keys.

  Take the example of an invoice detail table..

  It will contain the invoice number, number of rows , and other details..

  I need to validate whether the data in the number of rows column is correct.

  Hope u got the situation!!

  Thanks and Regards,

  Kindest Regards,
  Paarthasarathy
  Microsoft Certified Technology Specialist
  http://paarthasarathyk.blogspot.com

• Supriya.S

  Ten Centuries

  Points: 1131

  More actions

  August 19, 2009 at 9:10 am

  #1041625

  Try this:

  UPDATE test_table

  SET b='p'

  FROM

  (SELECT uq_column, count(*) cnt

  FROM test_table

  GROUP BY uq_column

  )TMP

  JOIN test_table b

  ON TMP.uq_column=b.uq_column AND TMP.cnt=b.cnt

  UPDATE test_table

  SET b='F'

  WHERE b'p'

  HTH,

  Sups

• Paarthasarathy

  SSChasing Mays

  Points: 649

  More actions

  August 19, 2009 at 9:26 am

  #1041645

  Exactly what i wanted....

  Perfect.. Superb.. Fantastic

  Thanks and Regards,

  Paartha

  My Brain stopped working Today 🙁

  Kindest Regards,
  Paarthasarathy
  Microsoft Certified Technology Specialist
  http://paarthasarathyk.blogspot.com