[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

UPDATE Based on select statement

Hi, I am trying to update a value of a table from a sub query, and I am not sure where I am going wrong.  Below is what I am trying to do, and the error message which I got

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
with BrickRatings
as
(
select (SELECT AVG(RATING) FROM Rating WHERE BrickID = B.BrickID) AS Rating, * FROM Brick B 
)
 
UPDATE Brick SET ThumbsUp = 1500 - (SELECT ROW_NUMBER() OVER (ORDER BY RATING DESC), * FROM BrickRatings WHERE BrickID = Brick.BrickID)

Open in new window

0
REA_ANDREW
Asked:
REA_ANDREW
  • 8
  • 7
  • 2
1 Solution
 
HuyBDCommented:
try this
with BrickRatings
as
(
select (SELECT AVG(RATING) FROM Rating WHERE BrickID = B.BrickID) AS Rating, * FROM Brick B 
)
 
UPDATE Brick SET ThumbsUp = 1500 - (SELECT ROW_NUMBER() OVER (ORDER BY RATING DESC), BrickRatings.* FROM BrickRatings WHERE BrickID = Brick.BrickID)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
with BrickRatings
as
(
  SELECT BrickID, AVG(RATING) FROM Rating WHERE BrickID GROUP BY BrickID
)
 
UPDATE Brick 
  SET ThumbsUp = 1500 - ROW_NUMBER() OVER (ORDER BY RATING DESC)
FROM Brick
JOIN BrickRatings br
  ON br.BrickID = Brick.BrickID

Open in new window

0
 
REA_ANDREWAuthor Commented:
HuyBD:
The error from your example was as follows:

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

angelIII:
The error from your example was as follows:

Msg 4145, Level 15, State 1, Line 4
An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'.

Thanks for your help so far
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
HuyBDCommented:
sorry
with BrickRatings
as
(
select (SELECT AVG(RATING) FROM Rating WHERE BrickID = B.BrickID) AS Rating, * FROM Brick B 
)
 
UPDATE Brick SET ThumbsUp = 1500 - (SELECT ROW_NUMBER() OVER (ORDER BY RATING DESC) FROM BrickRatings WHERE BrickID = Brick.BrickID)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry.
with BrickRatings
as
(
  SELECT BrickID, AVG(RATING) FROM Rating GROUP BY BrickID
)
 
UPDATE Brick 
  SET ThumbsUp = 1500 - ROW_NUMBER() OVER (ORDER BY RATING DESC)
FROM Brick
JOIN BrickRatings br
  ON br.BrickID = Brick.BrickID

Open in new window

0
 
REA_ANDREWAuthor Commented:
HuyBD:

Each row had equal ThumbsUp of 1499

angelIII:
The error from your example was as follows:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'BrickRatings'.

I Changed it to apply a column name and got this error

Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Thanks again for the help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see:
with BrickRatings as (
  SELECT BrickID, AVG(RATING) avg_rating FROM Rating GROUP BY BrickID
)
, br as ( 
  SELECT BrickID, avg_rating
    , row_number() over  (ORDER BY avg_RATING DESC) rn
  FROM BrickRatings
)
 
UPDATE Brick 
  SET ThumbsUp = 1500 - rn
FROM Brick
JOIN br
  ON br.BrickID = Brick.BrickID

Open in new window

0
 
REA_ANDREWAuthor Commented:
AngleIII that is nearly there.  When I do a select with AVERAGE Rating, and include Row Number,  Even though the AVG rating is the same, the Row Number differs.  i.e. is sequential,  what I need is for each thumb up to be 1500 - the records row number, so giving them all a different thumbs up.  Yours is very close, but assigns a large chuk of records the same number of thumbs up.

Thanks for your time

Andrew
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok:
with BrickRatings as (
  SELECT BrickID, AVG(RATING) avg_rating FROM Rating GROUP BY BrickID
)
, br as ( 
  SELECT BrickID, avg_rating
    , row_number() over  (ORDER BY avg_RATING DESC, brickID) rn
  FROM BrickRatings
)
 
UPDATE Brick 
  SET ThumbsUp = 1500 - rn
FROM Brick
JOIN br
  ON br.BrickID = Brick.BrickID

Open in new window

0
 
REA_ANDREWAuthor Commented:
Using your second example, only 720 rows are afftected and not the entire 1499 rows. Also there is still a large clump of data with the same thumbs up.

Cheers

Andrew
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Using your second example, only 720 rows are afftected and not the entire 1499 rows.
that means that not all BRICKID values from Brick table are in the Rating table ?!!

0
 
REA_ANDREWAuthor Commented:
ah right, ok, how about why a large number of of the thumbs up would be the same?

Could we identify those not in there using a left join perhaps instead of an inner JOIN?  as each should have a different thumb up.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have "duplicate" rows with the same brickid in the table brick?
if yes, what is the primary key in the bricks table?
0
 
REA_ANDREWAuthor Commented:
The Primary Key in the Brick Table is BrickID int autoincrement

if has no duplicates.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, you cannot get duplicate values for ThumbsUp ?!
did you add the brickid in the row_number() function?
, row_number() over  (ORDER BY avg_RATING DESC, brickID) rn

Open in new window

0
 
REA_ANDREWAuthor Commented:
I changed JOIN to LEFT JOIN in the Update statement and worked well enough for my needs.  On that note thank you for your time.  I appreciate it!  I will assign you points now.


0
 
REA_ANDREWAuthor Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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