locked
Insert value as per certain criteria RRS feed

  • Question

  • Lets say we have a table ACGrading where we store data (like a certain criteria to allot the grades accordingly), this table can be constructed using the code below.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ACGrading](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[MinMarks] [int] NULL,
    	[MaxMarks] [int] NULL,
    	[Grade] [varchar](500) NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO

    I had added some sample data to ACGrading table and now it is looking like this

    We have one more table ACMarks, where we have the information of Subjects and the marks obtained in that subject, the table can be constructed using the code below -

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ACMarks](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Subject] [varchar](500) NULL,
    	[Marks] [money] NULL,
    	[Grade] [varchar](500) NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO

    I had added some sample data to ACMarks table and now it is looking like this

    Now, kindly draw your attention to the Grade Column in ACMarks table. I want that for each record in ACMarks table, SQL should look Marks column and then compare the marks between MinMarks and MaxMarks in ACGrading table to retrieve the Grade and insert the associated Grade into the ACMarks table so that ACMarks table should look like this -

    Can we create a computed column or perform a certain query ? kindly help me with this.

    Saturday, March 25, 2017 2:59 PM

Answers

  • update [dbo].[ACMarks] set Grade = (select grade from [dbo].[ACGrading] where Marks between MinMarks and MaxMarks)

    • Marked as answer by Sid Williams Friday, June 16, 2017 9:11 AM
    Saturday, March 25, 2017 3:41 PM

All replies

  • Update m
    Set Grade = g.Grade
    From dbo.ACMarks m
    Inner Join dbo.ACGrading g On m.Marks Between g.MinMarks And g.MaxMarks;
    

    Note: the above is not tested.  Be sure to carefully test and that you have a good backup before running this against production data.

    Note also, that the above will not change the value of Grade if the Marks in AGMarks is outside of any range in AGGrading (for example, if someone had a Marks value of 65.

    Tom

    P.S. Thanks very much for providing the table definitions, but please do not supply sample data as an image.  Please supply that as INSERT statements into the tables.  Giving us the desired result as an image is fine.

    Saturday, March 25, 2017 3:25 PM
  • update [dbo].[ACMarks] set Grade = (select grade from [dbo].[ACGrading] where Marks between MinMarks and MaxMarks)

    • Marked as answer by Sid Williams Friday, June 16, 2017 9:11 AM
    Saturday, March 25, 2017 3:41 PM