more fast update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

more fast update

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 09:54:29

for example i have two tables

main table ( a ) ==================
|ID| |TOTAL_COUNT_OF a.ID IN table b|
------------------------------------
| 1| | 3 |
| 2| | 2 |
| 3| | 1 |
| 4| | 4 |

child table ( b )
=======================
|ID| |NAME |
----------------------
| 1| |1_name|
| 1| |1_name|
| 1| |1_name|
| 2| |2_name|
| 2| |2_name|
| 3| |3_name|
| 4| |4_name|
| 4| |4_name|
| 4| |4_name|
| 4| |4_name|

i try to

1) into table a, into each row of field TOTAL_COUNT_OF i must to write value = count(id) from table b

i see only one way, this way is cursor ,

declare MYCURSOR cursor for
select id from table a

open MYCURSOR

FETCH NEXT FROM MYCURSOR
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE table a
set TOTAL_COUNT_OF = (
SELECT COUNT(ID)
FROM table b
WHERE ID = @ID
)
WHERE ID = @ID

END

i now that this way is very basic (or maybe very wrong), someone can tell me how to normally and how to more logical i can write this task for me

if anybody can something write to me then thanks for you help

Edited by - marconi8 on 05/02/2003 09:57:45

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-02 : 10:16:44

NO, NO!! No cursors please!!

UPDATE a SET a.ChildCount = Sub1.ChildCount
FROM
a INNER JOIN
(SELECT ID, COUNT(*) AS ChildCount FROM b GROUP BY ID) AS Sub1
ON a.ID = Sub1.ID

Something like this should work, with a little modification to suit your requirements...

OS

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 11:20:32

oh nice, i have examinatëd your example and i have learned some new to me, thanks, in my original situation my code look like



UPDATE a 
SET a.OBIAVLENIA_COUNT = b.TOTAL_COUNT
FROM DOSKA_RAZDEL a LEFT JOIN 
	 (
	  SELECT RAZDEL_ID,COUNT(RAZDEL_ID) AS TOTAL_COUNT 
	  FROM DOSKA_OBIAVLENIA GROUP BY RAZDEL_ID		 
	 )AS b
ON a.ID=b.RAZDEL_ID

but i have next question,

all we know that null and 0 are not similar values interpreted by sql,
in interbase i have nice function , z function, this z function converted all noninteger values like null or '' into integer value "0"

in my code some of values return null value, how to made this, if returned value is null then this value is automatically converted to "0" integer value,

sorry for my basic question, i cannot still find in my city normal mssql /sql book

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-02 : 11:31:01

Look at the ISNULL() function. Very handy! don't leave home without it.

ISNULL(Value, ValueToReturnIfNUll)

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-02 : 11:38:03

Ok....but..........

How does:


SELECT RAZDEL_ID,COUNT(RAZDEL_ID) AS TOTAL_COUNT 
	  FROM DOSKA_OBIAVLENIA GROUP BY RAZDEL_ID

Return Nulls?

Try:


  SELECT TABLE_NAME, COUNT(*) AS NUM_COLS 
    FROM INFORMATION_SCHEMA.Columns
GROUP BY TABLE_NAME
GO

I don't think it ever will....If you're fgetting nulls, I would think there's a problem somewhere.

MOO

Brett

8-)

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 11:46:41

re>How does:

because in child table doesnt exists anything that will be connected to parent table

because i was using left join,
with inner join all is ok, i know

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 12:25:53

i have writed this code (with your help) and have a question this code is normally logicaly writed or i have maked some mistakes in sql logic

result of this code is next....

into table DOSKA_RAZDEL, into DOSKA_RAZDEL.obiavlenia_count must be returned count(razdel_id) from table DOSKA_OBIAVLENIA WHERE
DATEDIFF(d,GETDATE(),a.END_DATA)>0

but into DOSKA_RAZDEL.obiavlenia_count_new must be returned count(razdel_id) from table DOSKA_OBIAVLENIA WHERE
DATEDIFF(d,START_DATA,GETDATE())=0

results are returned to me normally, one word said all results are true,

 
UPDATE a 
SET a.OBIAVLENIA_COUNT = ISNULL(b.TOTAL_COUNT,0),
	a.OBIAVLENIA_COUNT_NEW = b.TOTAL_COUNT_TODAY
FROM DOSKA_RAZDEL a LEFT JOIN 
	 (
	  SELECT a.RAZDEL_ID,COUNT(a.RAZDEL_ID) AS TOTAL_COUNT,
						 (
						  SELECT COUNT(RAZDEL_ID)		
						  FROM DOSKA_OBIAVLENIA
 						  WHERE RAZDEL_ID=a.RAZDEL_ID
								AND
								DATEDIFF(d,START_DATA,GETDATE())=0
					     ) AS TOTAL_COUNT_TODAY
	  FROM DOSKA_OBIAVLENIA a
	  WHERE DATEDIFF(d,GETDATE(),a.END_DATA)>0
	  GROUP BY a.RAZDEL_ID		 
	 )AS b
ON a.ID=b.RAZDEL_ID

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-02 : 12:38:43

I'm sorry...having a little trouble with the english, and I'm not sure what you are asking for.

However, are you doing the left join to reset the values to 0?

Brett

8-)

Edited by - x002548 on 05/02/2003 12:40:02

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 12:49:47

yes, i am using left join because in child table values can be deleted independly, after this , for example trigger is started and when in left join results is returned null i convert this value to 0 by using function isnull

-----------------------------------------

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 13:40:57

problems with ON,WHERE statements

people, can anybody tell me this my construction of where statements and on statements is normal or something will be maked more better

 
UPDATE a
SET a.OBIAVLENIA_COUNT = ISNULL(b.TOTAL_COUNT,0)
FROM DOSKA_PODRUBRIKA a LEFT JOIN (	
								   SELECT RAZDEL_ID,RUBRIKA_ID,PODRUBRIKA_ID,
										  COUNT(PODRUBRIKA_ID) AS TOTAL_COUNT	
								   FROM DOSKA_OBIAVLENIA   
								   WHERE DATEDIFF(d,GETDATE(),END_DATA)>0	      
								   GROUP BY RAZDEL_ID,RUBRIKA_ID,PODRUBRIKA_ID	 	 			
								  ) AS b
ON a.ID=b.PODRUBRIKA_ID

WHERE a.ID=b.PODRUBRIKA_ID
	  AND
	  a.RAZDEL_ID=b.RAZDEL_ID
      AND
      a.RUBRIKA_ID=b.RUBRIKA_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources