SQL Update statement with 3 tables [Firebird]

by: rrjegan17Posted on 2009-04-17 at 23:40:17ID: 24174156

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

1:
2:
3:
4:
5:

Select all Open in new window

by: MerlaP83Posted on 2009-04-17 at 23:58:47ID: 24174213

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

1:
2:
3:

Select all Open in new window

by: rrjegan17Posted on 2009-04-18 at 03:55:14ID: 24174756

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

1:
2:
3:
4:

Select all Open in new window

by: MerlaP83Posted on 2009-04-18 at 08:18:21ID: 24175580

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
; "

by: FVERPosted on 2009-04-20 at 00:31:56ID: 24182495

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)

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

by: MerlaP83Posted on 2009-04-20 at 05:21:57ID: 24183812

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:

by: FVERPosted on 2009-04-20 at 05:31:10ID: 24183886

can you please provide create table statements for the 3 tables, and some data sample ?

by: MerlaP83Posted on 2009-04-20 at 05:40:30ID: 24183957

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)
);

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Select all Open in new window

by: FVERPosted on 2009-04-20 at 06:29:01ID: 24184333

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)

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

by: MerlaP83Posted on 2009-04-20 at 07:01:00ID: 24184645

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)

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

by: FVERPosted on 2009-04-20 at 07:12:27ID: 24184760

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

Question

SQL Update statement with 3 tables [Firebird]

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Interbase / Firebird Database

SQL Query Syntax

Delphi Database

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press