• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2011
  • Last Modified:

SQL Update statement with 3 tables [Firebird]

Hey all,

I need to update the table Spelare and field Lon, but to get the result I want I need information from the tables Division and Lag as well.

Everything works fine with the two first tables (Spelare + Lag), but when I try with the Division table in the end to get the final information I get some errors.

I am using Firebird.

Error: "SQL Error:  Dynamic SQL Error expression evaluation not supported. Error Code: -902. Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements The SQL: update spelare S set S.Lon = (S.Off*20)+(S.Def*10) + (SELECT (L.Rykte*15) FROM Lag L WHERE L.Klubb = S.Klubb) + (select (D.Division*15) from Division D join Lag L on L.Division = D.Division where L.Klubb = S.Klubb)
; "
update spelare S set S.Lon = (S.Off*20)+(S.Def*10) + (SELECT (L.Rykte*15) FROM Lag L WHERE L.Klubb = S.Klubb) + (select (D.Division*15) from Division D join Lag L on L.Division = D.Division where L.Klubb = S.Klubb)

Open in new window

0
MerlaP83
Asked:
MerlaP83
  • 5
  • 4
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Just try this out:
update spelare
set Lon = (SP.[OFF]*20)+(SP.Def*10) + L.Rykte*15 + D.[Division]*15
FROM spelare SP,Lag L,Division D
WHERE L.Klubb = SP.Klubb
AND L.Division = D.Division

Open in new window

0
 
MerlaP83Author Commented:
Looks like a smooth solution.

But at first it gave me errors because of the [ ], removed them and now it gives me: SQL Error:  Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 83 FROM. Error Code: -104. Invalid token The SQL:
update spelare set Lon = (SP.OFF*20)+(SP.Def*10) + L.Rykte*15 + D.Division*15 FROM spelare SP,Lag L,Division D
WHERE L.Klubb = SP.Klubb
AND L.Division = D.Division

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Have provided you SQL Server Syntax by mistake, Try this one out:

update spelare S, Lag L,Division D
set S.Lon = (S.Off*20)+(S.Def*10) + (L.Rykte*15) + (D.Division*15)
WHERE L.Klubb = S.Klubb 
AND L.Division = D.Division

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
MerlaP83Author Commented:
Darn, won't work.

Now this: "SQL Error:  Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 17 ,. Error Code: -104. Invalid token The SQL: update spelare S, Lag L,Division D set S.Lon = (S.Off*20)+(S.Def*10) + (L.Rykte*15) + (D.Division*15) WHERE L.Klubb = S.Klubb  AND L.Division = D.Division
; "
0
 
FVERCommented:
Hi,
please try this one
update spelare S
   set S.Lon = (S.Off*20)+(S.Def*10)
             + (SELECT (L.Rykte*15)
                     + case when D.Division is null then 0
                            else (D.Division*15)
                       end
                  FROM Lag L
                       left join Division D on L.Division = D.Division
                 WHERE L.Klubb = S.Klubb)

Open in new window

0
 
MerlaP83Author Commented:
Thanks for your reply and for people still trying to find a solution for this:) But I still get errors, now this:

SQL Error:  Dynamic SQL Error expression evaluation not supported. Error Code: -902. Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements The SQL:
0
 
FVERCommented:
can you please provide create table statements for the 3 tables, and some data sample ?
0
 
MerlaP83Author Commented:
Sure, I have provided some of the Create table statements below.

Example of  data from the table Spelare:
FNAMN: John, ENAMN: Anderson, Klubb: Man Utd, Off: 15, Def: 12, Alder: 25, Varde: 0

Example of  data from the table Lag:
Klubb: Man Utd, Nation: England, Division: Premier League, Arena: Old Trafford

Example of  data from the table Division:
Division: Premier League, Nationer: England, Rykte: 20
CREATE TABLE DIVISION (
  DIVISION      VARCHAR(30) CHARACTER SET ISO8859_1 DEFAULT 0 NOT NULL,
  NATIONER      VARCHAR(20) CHARACTER SET ISO8859_1 DEFAULT 0 NOT NULL,
  RYKTE         INTEGER DEFAULT 0,
);
 
CREATE TABLE LAG (
  KLUBB         VARCHAR(50) CHARACTER SET ISO8859_1 DEFAULT NULL COLLATE SV_SV,
  NATION        VARCHAR(30) CHARACTER SET ISO8859_1 DEFAULT NULL NOT NULL COLLATE SV_SV,
  DIVISION      VARCHAR(30) CHARACTER SET ISO8859_1 DEFAULT NULL NOT NULL COLLATE SV_SV,
  ARENA         VARCHAR(35) CHARACTER SET ISO8859_1 DEFAULT 0 NOT NULL
);
 
CREATE TABLE SPELARE (
  ID            INTEGER NOT NULL,
  FNAMN         VARCHAR(30) CHARACTER SET ISO8859_1 COLLATE SV_SV,
  ENAMN         VARCHAR(30) CHARACTER SET ISO8859_1 COLLATE SV_SV,
  KLUBB         VARCHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE SV_SV,
  ALDER         INTEGER DEFAULT 20 NOT NULL,
  POS           VARCHAR(1),
  OFF           INTEGER DEFAULT 0 NOT NULL,
  DEF           INTEGER DEFAULT 0 NOT NULL,
  INSLAPPTA     INTEGER DEFAULT 0,
  VARDE         INTEGER DEFAULT 0,
  BOOST         INTEGER DEFAULT 0,
  /* Keys */
  CONSTRAINT PK_SPELARE
    PRIMARY KEY (ID)
);

Open in new window

0
 
FVERCommented:
In your srcrit I had to move RYKTE column from DIVISION to LAG table and add a LON column in SPELARE to be coherent with the query we want to debug.
Anyway, It seems that the problem comes from the datatypes.
D.DIVISION and L.RYKTE are created as VARCHAR while you are trying to use them as numbers.

Please try the query below.

Also note that the last part of the error message is often the more useful, as in the example below (from flamerobin's output)

*** IBPP::SQLException ***
Context: Statement::Prepare( update spelare S
set S.Lon = (S.Off*20)+(S.Def*10)
+ (SELECT (cast(L.Rykte as integer)*15)
+ case when D.Division is null then 0
else (cast(D.Division as integer)*15)
end
FROM Lag L
left join Division D on L.Division = D.Division
WHERE L.Klubb = S.Klubb)
)
Message: isc_dsql_prepare failed

SQL Message : -206
Column does not belong to referenced table

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
S.LON
At line 2, column 10


update spelare S
   set S.Lon = (S.Off*20)+(S.Def*10)
             + (SELECT (cast(L.Rykte as integer)*15)
                     + case when D.Division is null then 0
                            else (cast(D.Division as integer)*15)
                       end
                  FROM Lag L
                       left join Division D on L.Division = D.Division
                 WHERE L.Klubb = S.Klubb)

Open in new window

0
 
MerlaP83Author Commented:
Got it working now, but with some changes (D.Division needs to be a Varchar since it holds which league the team's play in).

Thanks for your help.

The code below worked, just one more thing before I give you the points. If I want to have an extra calcuation if L.Division is 'NHL' ? How would I go on to do this?

Something like:

+ case when L.Division = 'NHL' then
               (cast(L.Rykte as integer)*50)

?

Adding that one will make the query jump all other "L.Divisions" and only set the ones with 'NHL'
update spelare S
   set S.Lon = (S.Off*20)+(S.Def*10)
             + (SELECT (cast(L.Rykte as integer)*15)
                     + case when D.Rykte is null then 0
                            else (cast(D.Rykte as integer)*15)
                       end
                  FROM Lag L
                       left join Division D on L.Division = D.Division
                 WHERE L.Klubb = S.Klubb)

Open in new window

0
 
FVERCommented:
just add
 + case when L.Division = 'NHL' then (cast(L.Rykte as integer)*50)
             else 0
    end

the "else" part will apply to the other divisions
             
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now