Conditional Update of a field from multiple tables

 

Dear all,

I have a target table with two fields: Date and ID

There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score

 

Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.

 

Could anyone kindly help me or give me some hints? Thanks

 

Regards

Roscol

Roscol

First of all build a SELECT query that returns  highest Score based on ID , since you have not provided sample data + desired result i guess you need something like that

WITH cte

AS

(

SELECT ID, CAST(dt AS DATE) DT, MAX(Score)   FROM s1

GROUP BY id,CAST(dt AS DATE)

) UPDATE target SET ID=cte.ID FROM cte JOIN target ON cte.dt=target.date

Dear Dimant,

Thanks for your reply. I do not get your idea yet. The max involves comparing three tables (STable1,STable2,STable3), I only found one table in your code. Can you help me again? I am new to SQL server, thanks.

Sample srouce tables and target table are shown as follows:

STable1
Date                Name                    Score
2012-08-15    AG1209.SHF          4226
2012-08-16    AG1209.SHF          3732
2012-08-17    AG1209.SHF          6234
2012-08-20    AG1209.SHF          4554
2012-08-21    AG1209.SHF          10510
2012-08-22    AG1209.SHF          12372
2012-08-23    AG1209.SHF          23580
2012-08-24    AG1209.SHF          8786

STable2
Date                Name                   Score
2012-08-15    AG1210.SHF          80
2012-08-16    AG1210.SHF          24
2012-08-17    AG1210.SHF          7828
2012-08-20    AG1210.SHF          2
2012-08-21    AG1210.SHF          36
2012-08-22    AG1210.SHF          24
2012-08-23    AG1210.SHF          98
2012-08-24    AG1210.SHF          74

STable3
Date                Name                   Score

2012-08-15    AG1211.SHF          18
2012-08-16    AG1211.SHF          4
2012-08-17    AG1211.SHF          16
2012-08-20    AG1211.SHF          22
2012-08-21    AG1211.SHF          18
2012-08-22    AG1211.SHF          18
2012-08-23    AG1211.SHF          134
2012-08-24    AG1211.SHF          13000

TargetTable i.e. Results

Date                Name 

2012-08-15    AG1209.SHF        
2012-08-16    AG1209.SHF        
2012-08-17    AG1210.SHF         
2012-08-20    AG1209.SHF         
2012-08-21    AG1209.SHF         
2012-08-22    AG1209.SHF         
2012-08-23    AG1209.SHF         
2012-08-24    AG1211.SHF        

• Edited by Bobojin_Z 21 hours 40 minutes ago

Yes, I provide an example only for one table, as I am guessing you can use UNION ALL to combine all tables, bit moreover, it is not clear what is a desired result from your example 

WITH cte

AS

(

SELECT ID, CAST(dt AS DATE) DT, MAX(Score)   FROM s1

GROUP BY id,CAST(dt AS DATE)

UNION ALL

SELECT ID, CAST(dt AS DATE) DT, MAX(Score)   FROM s2

GROUP BY id,CAST(dt AS DATE)

UNION ALL

SELECT ID, CAST(dt AS DATE) DT, MAX(Score)   FROM s3

GROUP BY id,CAST(dt AS DATE)

) UPDATE target SET ID=cte.ID FROM cte JOIN target ON cte.dt=target.date

;with cte as (select [Date],[Name],[Score] from STable1 UNION 

SELECT [Date],[Name],[Score] from STable2 UNION SELECT [Date],[Name],[Score] from STable3), cte2 as (select *, row_number() over (partition by [Date]

order by Score DESC) as Rn from cte2)

MERGE Table1 as target

using (select * from cte2 where Rn =1) as src on target.[Date] = src.[Date]

WHEN Matched THEN Update SET [Name] = src.[Name]

>> I have a target table with two fields [sic] : Date [sic] and ID [sic] <<

You might want to read a book on SQL. A field is part of a temporal value {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND}. I think you meant column. 

DATE is a reserved word and a violation of ISO-11179 rules. The magical generic id is really bad SQL and a violation of ISO-11179 rules. Is it the key of Targets? I had to give your table a name, since you did not.  The terms target and source are part of the MERGE  statement definition. 

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

>>  There three source tables: S1, S2, S3, each of them has three fields [sic]: Date [sic], ID [sic], and Score [sic] <<

In RDBMS and basic data modeling, use <attribute>_<attribute property> syntax. Is this what you meant? Did you know that a table has to have a key? 

CREATE TABLE Test_1
(test_date DATE NOT NULL,
 student_id CHAR(10) NOT NULL,
 PRIMARY KEY (test_date, student_id),
 test_score INTEGER DEFAULT 0 NOT NULL
  CHECK (test_score BETWEEN 0 AND 100)
);

CREATE TABLE Test_2
(test_date DATE NOT NULL,
 student_id CHAR(10) NOT NULL,
 PRIMARY KEY (test_date, student_id),
 test_score INTEGER DEFAULT 0 NOT NULL
  CHECK (test_score BETWEEN 0 AND 100)
);

CREATE TABLE Test_3
(test_date DATE NOT NULL,
 student_id CHAR(10) NOT NULL,
 PRIMARY KEY (test_date, student_id),
 test_score INTEGER DEFAULT 0 NOT NULL
  CHECK (test_score BETWEEN 0 AND 100)
);


This design is wrong. This is a maqjort desgin flaw called attribute splitting. Would you put shoe sizes in one table, hat sizes in another and weight in a third table then try to bring them together? NO! These are all attributes of the same entity, so they belong in the table that models that entity. 

Here is the correct schema: 

CREATE TABLE Test_Scores
(test_date DATE NOT NULL,
 student_id CHAR(10) NOT NULL,
 PRIMARY KEY (test_date, student_id),
 test_score_1 INTEGER DEFAULT 0 NOT NULL
  CHECK (test_score_1 BETWEEN 0 AND 100),
 test_score_2 INTEGER DEFAULT 0 NOT NULL
  CHECK (test_score_2 BETWEEN 0 AND 100),
 test_score_3 INTEGER DEFAULT 0 NOT NULL
  CHECK (test_score_3 BETWEEN 0 AND 100)
);

>> Now I want to update the target table: put the ID [sic] into the ID field [sic] which has the highest [something] Score from the three tables on each day. << 

Your mindset is still back in the 1970's. We do not have to materialize data in the year 2015! Use a VIEW that will be always correct and not waste disk. 

CREATE VIEW Best_Scores (test_date, student_id, best_test_score)
AS
SELECT test_date, student_id,
       CASE WHEN test_score_1 >= test_score_2 
                  AND test_score_1 >= test_score_3
            THEN test_score_1
            WHEN test_score_2 >= test_score_1 
                  AND test_score_2 >= test_score_3
            THEN test_score_2
            WHEN test_score_3 >= test_score_2 
                  AND test_score_3 >= test_score_1
            THEN test_score_3
            ELSE NULL
   FROM Test_Scores; 

  

 I am new to SQL server, thanks.

How many weeks have you been studying SQL? I teach SQL, so I need to understand how and why people make errors.  This kind of design error is usually made less than 3 weeks into a basic SQL course.  

These types of problems are much easier to solve when you provide the DDL and example data.

Try this example:

DECLARE @target TABLE (date DATE, ID int)

DECLARE @s1 TABLE (date DATE, ID INT, score INT)
DECLARE @s2 TABLE (date DATE, ID INT, score INT)
DECLARE @s3 TABLE (date DATE, ID INT, score INT)

INSERT INTO @s1 (date, ID, score) VALUES
('1900-01-01', 1, 5),('1900-01-01', 2, 1),('1900-01-01', 3, 3),('1900-01-01', 4, 5),('1900-01-01', 5, 1)

INSERT INTO @s2 (date, ID, score) VALUES
('1900-01-02', 1, 3),('1900-01-02', 2, 5),('1900-01-02', 3, 1),('1900-01-02', 4, 3),('1900-01-02', 5, 5)

INSERT INTO @s3 (date, ID, score) VALUES
('1900-01-03', 1, 1),('1900-01-03', 2, 3),('1900-01-03', 3, 5),('1900-01-03', 4, 1),('1900-01-03', 5, 3)

;WITH updateFrom AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY score DESC) AS seq
  FROM (
        SELECT *
          FROM @s1
         UNION ALL
        SELECT *
          FROM @s2
         UNION ALL
        SELECT *
          FROM @s3
       ) a
)

INSERT INTO @target (date, ID)
SELECT date, ID
  FROM updateFrom
 WHERE seq = 1

SELECT *
  FROM @target

2~3 days, any suggested books for read?

Reagards
Roscol

• Edited by Bobojin_Z 5 hours 12 minutes ago

Patrick,

Thank you so much, I have modified my code based on your idea and now it works. I have to mark yours as the answer.
If I want to know more about the operations like the commands 'PARTITION BY ID ORDER BY score DESC', which books/documents should I read?

Thank you once again.

Regards,

Roscol

• Edited by Bobojin_Z 4 hours 54 minutes ago

Thank you Naomi for your suggestions and examples.
I marked Patrick's solution as answer.
Because there will be many WHEN-THEN commands in the case of many source tables. Thank you once again.

Regards,

Roscol

Thank you for your suggestions, I will hava a look on the MERGE operation.

Regards,
Roscol