3 Replies - 331 Views - Last Post: 04 December 2013 - 11:41 PM Rate Topic: -----

#1 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Invalid SQL trigger?

Posted 03 December 2013 - 10:46 AM

It's a calculator, to calculate the weightgroup of a certain weight where an order falls into. The beginweight category has values: 0,0 ; 1,0; 2,0; 5,0 ; 10,0
the endweight category has values: 1,0 ; 2,0; 5,0 ; 10,0

the pseudocode formula is
Weightgroup.beginweight<= weight< Weightgroup.endweight

So in SQL I think it's something like this, but i could be off:
Update Order O 
set O.weightgroup = (select O.weight
from Weightgroup W join Order O 
on
W.nr = O.weightgroup

where W.beginweight<= O.weight and O.weight < W.endweight



It doesn't give any errors, but no value appears in O.weightgroup either.

Is This A Good Question/Topic? 0
  • +

Replies To: Invalid SQL trigger?

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3243
  • View blog
  • Posts: 10,882
  • Joined: 12-December 12

Re: Invalid SQL trigger?

Posted 03 December 2013 - 11:31 AM

It should give errors because you are missing a bracket and Order is a reserved keyword in most databases. It would be far better to rename this table but, to use this keyword, surround Order with delimiters. The delimiters might be back ticks ` or square-brackets, depending on the database you are using.
Was This Post Helpful? 1
  • +
  • -

#3 Random01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 06-October 13

Re: Invalid SQL trigger?

Posted 03 December 2013 - 02:27 PM

it's in another language originally so Order isn't the actual name. But that's good to know for in the future anyway thanks.

and i missed a bracket when translating, so in the actual code the bracket is present
Was This Post Helpful? 0
  • +
  • -

#4 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 352
  • View blog
  • Posts: 770
  • Joined: 27-June 09

Re: Invalid SQL trigger?

Posted 04 December 2013 - 11:41 PM

I am assumming the Order.weightgroup starts out blank, so when you join Order O on W.nr = O.weightgroup the result is blank because W.nr never equals any O.weightgroup. The way to get the correct update syntax is to first write a select statement to select the values

select O.OrderId, O.weightgroup, W.nr
from Weightgroup W
inner join Order O
on W.beginweight<=O.weight and O.weight<W.endweight



The result should be the order, a blank weightgroup, and what value that weightgroup should be. Then copy portions of the select code and fill in the following template

update /*TableAlias*/
set /*UpdateTableFields*/ = /*SelectStatementFields*/
from /*select statement's "from" clause*/


Your code should look something like

update O
set O.weightgroup = W.nr
from Weightgroup W
inner join Order O
on W.beginweight<=O.weight and O.weight<W.endweight


This will populate the weightgroup of every order
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1