7 Replies - 4107 Views - Last Post: 02 March 2011 - 03:02 AM Rate Topic: -----

#1 Rohella   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 24
  • Joined: 05-November 10

SQL SELECT UPDATE query

Posted 21 February 2011 - 04:45 AM

Hi Guys,
I needed help on an SQL query I have. I have the select query working fine but according to the result I need to set the column of a table to 0. I have the following Select query:

SELECT     CommissionCalculator.AgentName, CommissionCalculator.GroupNo, CommissionCalculator.InvoiceDate, CommissionCalculator.SetUpFee, CommissionTotal.Paid, 
                      CommissionCalculator.Commission
FROM         CommissionTotal INNER JOIN
                      CommissionCalculator ON CommissionTotal.AgentName = CommissionCalculator.AgentName 
                      WHERE CommissionCalculator.SetUpFee >0


I need it to update the CommissionStructure table e.g (This code is wrong but hopefully it helps you understand what I am trying to do)

UPDATE CommissionStructure
(SELECT     CommissionCalculator.AgentName, CommissionCalculator.GroupNo, CommissionCalculator.InvoiceDate, CommissionCalculator.SetUpFee, CommissionTotal.Paid, 
                      CommissionCalculator.Commission
FROM         CommissionTotal INNER JOIN
                      CommissionCalculator ON CommissionTotal.AgentName = CommissionCalculator.AgentName 
                      WHERE CommissionCalculator.SetUpFee >0)
SET SetUp = 0



So where the CommissionCalculator SetUp > 0 I want it to Update the CommissionStructure SetUp column to 0.

I was thinking I might need an IF statement but I dont know how to work with them in SQL.
Can anyone help me please

Thank you so much

This post has been edited by Rohella: 21 February 2011 - 04:47 AM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL SELECT UPDATE query

#2 raziel_   User is offline

  • Like a lollipop
  • member icon

Reputation: 469
  • View blog
  • Posts: 4,280
  • Joined: 25-March 09

Re: SQL SELECT UPDATE query

Posted 21 February 2011 - 05:33 AM

would it not be easier to save the result from select statement in datatable and update it using datatable?
Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: SQL SELECT UPDATE query

Posted 21 February 2011 - 06:15 AM

Not sure I understand completely but
couldn't you just do
UPDATE CommissionStructure SET SetUp = 0 WHERE SetUpFee > 0


Your select statement is selecting all records WHERE SetUpFee in CommissionStructure is > 0

Since you are not specifying anything else in your WHERE Clause, The UPDATE statement above will use its WHERE Clause to update those same records.

This post has been edited by CharlieMay: 21 February 2011 - 06:18 AM

Was This Post Helpful? 0
  • +
  • -

#4 Rohella   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 24
  • Joined: 05-November 10

Re: SQL SELECT UPDATE query

Posted 21 February 2011 - 07:39 AM

View PostCharlieMay, on 21 February 2011 - 06:15 AM, said:

Not sure I understand completely but
couldn't you just do
UPDATE CommissionStructure SET SetUp = 0 WHERE SetUpFee > 0


Your select statement is selecting all records WHERE SetUpFee in CommissionStructure is > 0

Since you are not specifying anything else in your WHERE Clause, The UPDATE statement above will use its WHERE Clause to update those same records.


@CharlieMay SetUpFee is a column in another table which makes it difficult, which is why I need an inner join.

View PostNoBrain, on 21 February 2011 - 05:33 AM, said:

would it not be easier to save the result from select statement in datatable and update it using datatable?


@No Brain: That makes it easier to update the record but it'll be another table with repeatative data. I might do it this way if I cant get the solution I am looking for.

@Both - thank you so much for your replies.
Was This Post Helpful? 0
  • +
  • -

#5 raziel_   User is offline

  • Like a lollipop
  • member icon

Reputation: 469
  • View blog
  • Posts: 4,280
  • Joined: 25-March 09

Re: SQL SELECT UPDATE query

Posted 21 February 2011 - 07:46 AM

so in short you want to use select in where clause and update it. check this link out:
http://en.wikipedia....ki/Update_(SQL)
Was This Post Helpful? 0
  • +
  • -

#6 Rohella   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 24
  • Joined: 05-November 10

Re: SQL SELECT UPDATE query

Posted 21 February 2011 - 08:31 AM

View PostNoBrain, on 21 February 2011 - 07:46 AM, said:

so in short you want to use select in where clause and update it. check this link out:
http://en.wikipedia....ki/Update_(SQL)


Hi Guys I just solved it right before NoBrain message. Thank you so much for your help guys. @NoBrain that is what I was looking for thank you LOL! :bananaman:
Heres the end result and its working perfectly

UPDATE CommissionStructure SET CommissionStructure.SetUp = 0 FROM CommissionStructure INNER JOIN CommissionCalculator 
ON CommissionStructure.GroupNo = CommissionCalculator.GroupNo
WHERE CommissionStructure.GroupNo = CommissionCalculator.GroupNo AND CommissionCalculator.SetUpFee >0


Cheers Guys
x
Was This Post Helpful? 2
  • +
  • -

#7 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: SQL SELECT UPDATE query

Posted 21 February 2011 - 09:04 AM

Quote

@CharlieMay SetUpFee is a column in another table which makes it difficult, which is why I need an inner join.


Ahh, sorry, I totally missed that.

Anyway, glad you got it working :)
Was This Post Helpful? 0
  • +
  • -

#8 Rohella   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 24
  • Joined: 05-November 10

Re: SQL SELECT UPDATE query

Posted 02 March 2011 - 03:02 AM

View PostCharlieMay, on 21 February 2011 - 09:04 AM, said:

Quote

@CharlieMay SetUpFee is a column in another table which makes it difficult, which is why I need an inner join.


Ahh, sorry, I totally missed that.

Anyway, glad you got it working :)


Thats no problem, really appreciate your replies :) Thank you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1