Solved

UPDATE Based on select statement

Posted on 2008-10-03
17
268 Views
Last Modified: 2010-04-21
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
Comment
Question by:REA_ANDREW
  • 8
  • 7
  • 2
17 Comments
 
LVL 17

Expert Comment

by:HuyBD
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 20

Author Comment

by:REA_ANDREW
Comment Utility
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
 
LVL 17

Expert Comment

by:HuyBD
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 20

Author Comment

by:REA_ANDREW
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 20

Author Comment

by:REA_ANDREW
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 20

Author Comment

by:REA_ANDREW
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 20

Author Comment

by:REA_ANDREW
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 20

Author Comment

by:REA_ANDREW
Comment Utility
The Primary Key in the Brick Table is BrickID int autoincrement

if has no duplicates.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 20

Author Comment

by:REA_ANDREW
Comment Utility
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
 
LVL 20

Author Closing Comment

by:REA_ANDREW
Comment Utility
Thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now