CODEWITHSUNDEEP
sqlsql-server-ce
Bk_
Bk_

Reputation: 99

Change from SQL to SQL-CE - Syntax

I've already asked a question for the following code: Here is the question.

I have the following sql syntax that I used in my database query (SQL Server).

MERGE INTO table_with_team_and_goals AS target
USING (SELECT Team, SUM(headergoal + freekickgoal) AS goals FROM table_with_goals GROUP BY team) AS source
ON target.team=source.team
 WHEN MATCHED THEN 
        UPDATE SET goals = source.goals
    WHEN NOT MATCHED THEN   
        INSERT (Team, Goals)
        VALUES (source.team, source.goals);

I'm changing now to sql-server-ce (compact edition). I could find out that, some commands which are supported by sql aren't supported by SQL Server Compact. (like marge into)

How can I change the code, that it works on SQL Server Compact.

Thank you for the help! BK

Edit:

The Table: table_player_goal_ratings -> contains following data:

 ID......Team...........Name......HeaderGoal......FreeKickGoal
 104     Barcelona      Mike      2               1
 105     Barcelona      Peter     0               0
 106     Real Madrid    Michael   0               2
 107     Real Madrid    Terry     0               2
 108     Chelsea        James     0               0
 109     Chelsea        Arthur    1               2
 110     Chelsea        Spence    1               2

Now i want to insert in following table(table_team_goal_ratings) these data like this:

 FC.............Goals
 Barcelona      3
 Real Madrid    4
 Chelsea        6

Upvotes: 0

Views: 564

Answers (1)

Lamak
Lamak

Reputation: 70678

You can use UPDATE and INSERT:

BEGIN TRANSACTION;

UPDATE A
SET goals = B.goals
FROM dbo.table_with_team_and_goals A
INNER JOIN (SELECT team, SUM(headergoal + freekickgoal) AS goals 
            FROM table_with_goals 
            GROUP BY team) B
    ON A.team = B.team

INSERT INTO dbo.table_with_team_and_goals(team, goals) 
SELECT team, goals
FROM (  SELECT team, SUM(headergoal + freekickgoal) AS goals 
        FROM table_with_goals 
        GROUP BY team) A
WHERE NOT EXISTS(SELECT 1 FROM dbo.table_with_team_and_goals
                 WHERE team = A.team)

COMMIT TRANSACTION;

Upvotes: 5

Related Questions