UPDATE query from 2 SELECT statments

• akhlaq768

  SSChasing Mays

  Points: 642

  More actions

  November 3, 2010 at 4:57 am

  #228712

  I need help creating an UPDATE query.

  I want to use the results of the two SELECT statements to UPDATE another table

  So for example, my 2 SELECT statements are as follows.

  SELECT

  COUNT(*)/8

  FROM myTable1

  WHERE class = 'ART'

  Returns 62

  SELECT

  COUNT(*)

  FROM myTable2

  WHERE class = 'ART'

  128

  Below is my UPDATE query. Im not quite sure what to put inbetween the open/clossed brackets.

  I want to place a 1 if results from table 2 is greater than table 1

  INSERT INTO dbo.myNewTable

  SELECT RTRIM(LTRIM('Art')), RTRIM(LTRIM('Incidents')), +

  (

  ),''

  If 128 is greater than 62 then put a 1 otherwise put a 0

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 3, 2010 at 5:29 am

  #1245921

  you can use a CASE to decide which value to insert:(note you said update, but it's really an insert in your example)

  INSERT INTO dbo.myNewTable

  SELECT

  RTRIM(LTRIM('Art')),

  RTRIM(LTRIM('Incidents')),

  CASE

  WHEN SOMEVALUE > 128

  THEN 1

  ELSE 0

  END,

  CASE

  WHEN COUNT(*) > 128

  THEN COUNT(*) / 8

  WHEN COUNT(*) = 0

  THEN 0

  ELSE 4

  END

  ''

  FROM SOMETABLE

  the "SOMEVALUE" part could be the count(*) / 8 calculation, or something more complex.

  akhlaq768 (11/3/2010)

  ---

  I need help creating an UPDATE query.

  I want to use the results of the two SELECT statements to UPDATE another table

  So for example, my 2 SELECT statements are as follows.

  SELECT

  COUNT(*)/8

  FROM myTable1

  WHERE class = 'ART'

  Returns 62

  SELECT

  COUNT(*)

  FROM myTable2

  WHERE class = 'ART'

  128

  Below is my UPDATE query. Im not quite sure what to put inbetween the open/clossed brackets.

  I want to place a 1 if results from table 2 is greater than table 1

  INSERT INTO dbo.myNewTable

  SELECT RTRIM(LTRIM('Art')), RTRIM(LTRIM('Incidents')), +

  (

  ),''

  If 128 is greater than 62 then put a 1 otherwise put a 0

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• akhlaq768

  SSChasing Mays

  Points: 642

  More actions

  November 3, 2010 at 5:47 am

  #1245927

  thanks... perfect

• madhuprakashb

  Grasshopper

  Points: 14

  More actions

  November 3, 2010 at 5:53 am

  #1245930

  Hi Please Check is this what you expects.

  Declare @sample1-2 table

  (

  Data1 int

  )

  DEclare @Sample2 table

  (

  Data1 int

  )

  Declare @Sample3 table

  (

  Data1 varchar(max),

  Data2 varchar(max),

  Data3 varchar(max)

  )

  Declare @i int

  Set @i = 1

  While @i < = 150

  Begin

  Insert into @sample1-2 values (@i)

  set @i = @i + 1

  End

  Set @i = 1

  While @i < = 128

  Begin

  Insert into @Sample2 values (@i)

  set @i = @i + 1

  End

  Select COUNT(*) from @sample1-2

  Select COUNT(*) from @Sample2

  Insert into @Sample3

  SELECT RTRIM(LTRIM('Art')), RTRIM(LTRIM('Incidents')), +

  (case when ((Select COUNT(*) from @sample1-2) > (Select COUNT(*) from @Sample2)) then 1 else 0 end)

  Select * from @Sample3

  Regards

  Madhu

• akhlaq768

  SSChasing Mays

  Points: 642

  More actions

  November 3, 2010 at 8:43 am

  #1246042

  yes Madhu, its right