Count Query Xyntax Help 2

[AnonGod]

Hey there -

Trying to query some data and having a hard time getting my head around what needs to be done. I'm fairly sure I need a nested query, but just can't see where to put it...

Existing data sample:
[tt]
col1 col2 col3
Cat Hat Book
Big Hit Movie
Cool Beans Phrase
Cat Hat Book
Big Hit Movie
Cat Hat Book
Big Hit Movie
Cool Beans Phrase
Cat Hat Book
[/tt]

Here's the format I want the data returned in:
[tt]
col1 col2 col3 col4
Cat Hat Book 4
Big Hit Movie 3
Cool Beans Phrase 2
[/tt]

Col4 is a count column of the # of entries.

I keep thinking this is something simple, but the queries i've tried keep returning syntax errors. I'm just not getting it.

I need this in pure SQL syntax.

Any help is appreciated.

Thanks!
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

hey, sorry, but it's only updating one of the votes and none of the others...

So in the above sample data, it is updating colName to 'Mouse with no name' but NOT updating colPrice to 25.

Any ideas?

Thanks again
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

Worked fine for me when I tested the code yesterday and it updated 2 rows for me. I do not have the tables and insert scripts right now but

If you can paste the code that you are running, I will let you know if you need any changes.

Regards,
AA

 

[AnonGod]

Actually, I've only tested it so far as to try 2 updates on the same main record.

So 5 votes to change the name and description for the same main record. It only changes the first column name it comes to.

I'll get what I have and post back here shortly.

-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

I think, I know what the problem is.

The query works fine if a given id does not appear more than once.

When it appears more than once:
It does both the updates but since we are resetting the colvalues to themselves if they do not match, they are getting overridden with the old value.

In this example:
for 66 the colDescription is updated first
then for the same table colName is updated and also the colDescription is updated back to old value (since that is the value main had.

Let me think over how to resolve this. I am trying to do this with a single query (and to not use dynamic sql) as of now.

Regards,
AA

 

[amrita418]

Here is the code that uses a cursor to update the table.
If your data set is not huge then you should have no issues running this. If you dataset is huge then you can use temp tables instead of cursors to store the data and then update the main table back.

DECLARE
@v_id 	      INT,
@v_colName    VARCHAR(30),
@v_colValue   VARCHAR(50),
@v_query      VARCHAR(200)

DECLARE c1 CURSOR FOR SELECT  	b.vote_id, b.votecolumnname, b.votenewvalue
		      FROM    	main m, 
				(SELECT  vote_id, votecolumnname, votenewvalue
        			 FROM       dbo.voting
        			 GROUP BY   vote_id, votecolumnname, votenewvalue
        			 HAVING     COUNT(*) >= 5) b
		      WHERE 	m.item_id = b.vote_id

OPEN 	c1
SET 	@v_id = 0
SET 	@v_colName = ''
SET 	@v_colValue = ''
SET 	@v_query = ''

FETCH 	NEXT FROM c1 INTO    @v_id, @v_colName, @v_colValue
WHILE 	@@FETCH_STATUS = 0
BEGIN
	SET 	@v_query = 'UPDATE MAIN SET ' + @v_colName + 
                           ' = ''' + @v_colValue + 
                           ''' WHERE ITEM_ID = ' + CONVERT(VARCHAR, @v_id)
	EXEC	(@v_query)
	SET 	@v_id = 0
	SET 	@v_colName = ''
	SET 	@v_colValue = ''
	SET 	@v_query = ''
	FETCH 	NEXT FROM c1 INTO   @v_id, @v_colName, @v_colValue
END
CLOSE 	c1
DEALLOCATE c1

Hope this helps
Regards,
AA

 

[AnonGod]

Ah, I see - Thanks for the cursor code, I'm using that now.

Now, (sorry to keep throwing these out there!) I've added another EXEC query on each loop to update a table called 'AutoUpdate' that has the date and time updated, the ITEM_ID, the colName, and the before and after values.

We are pulling in everything I need except the value of the column before it's updated. Everything is ready to go, I just need to pull in the existing value into a variable.

I pulled this from msdn:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_0vs5.asp

But it does not want to accept a dynamic SQL query.
Can you help me with my syntax (or whatever might do this - seems like a smiple thing)? - This is kinda what I need:
SET @v_oldvalue = ('SELECT ' + @v_column + ' FROM main WHERE item_id = " + CONVERT(varcher, @v_id)

?
Thanks Again!!!
-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

If you are just trying to run the query you mentioned

I see two errors with it:
1) you have an extra code after item_id
2) varchar is mis-spelled.

try this:

SET @v_oldvalue = 'SELECT ' + @v_column + ' FROM main WHERE item_id = ' + CONVERT(varchar, @v_id)

Regards,
AA

 

[AnonGod]

Yeah, sorry about the mis-spellings.

The code you gave me will set the @v_oldvalue to the query string. What I'm looking for is for the query to run and return a value into the @v_oldvalue variable.

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[AnonGod]

So it's almost like I need:

SET @v_query = 'SELECT ' + @v_column + ' FROM main WHERE item_id = ' + CONVERT(varchar, @v_id)

SET @v_oldvalue = EXEC(@v_query)

Thanks! -Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

Sorry about the confusion. Code below should do what you need.

declare @v_query      NVARCHAR(200) -- needs to be nvarchar
select @v_query = N'SELECT @out = ColName from Main where item_id = ' + CONVERT(varchar(5), @v_id)

exec sp_executesql @v_query, N'@out varchar(30) OUTPUT', @v_oldValue OUTPUT

PRINT 'Old Value = ' + @v_oldValue

Regards,
AA

 

[AnonGod]

Absolutely brilliant. Thank you so much.

I'd give you 20 stars if I could.

-Andrew

[Signature modified by admin request]
-TAG
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[amrita418]

Glad I could help.
Happy coding

Regards,
AA