Update Syntax

the best thing for the questioner to do would be to say what database is being used ...
because ... this update statement will not work for sql server
next thing would be to supply decent data example

for sqlserver:
update yourtable
set childnum = c.num_children
from yourtable t1,
  (select parentno, count(*) num_children from yourtable group by parentno) c on t1.idno = c.parentno

for oracle:
use above statement :)

@petr_hlucin
I get an error - "Parameter t1.ChildNum has no default value."

@Geert_Gruwez
Forgot to mention - Need it for Access

the sample data you provided is not sufficient. can you provide more data.

@Sharath_123
Tab issues...

IdNo      Name      ParentNo            ChildNum
1
2                                1
3
4                                1
5

The update command should set the value of ChildNum for idNo = `1 to 2 because
IdNo 2 & 4 have a value of 1 as ParentNo

update table x set childnum=(select count(*) from x where name=1) where idnum=1

@relishguy
Please read comments on ID:23249188

The data example provided is for the purpose of understanding my question.
The actual table has over 6 millions records where some of the records have childs records and some have none.

I tested my solution by changing the ParentNo from NULL to 1,2,3,4 or 5 in the sample date and its working fine. check it and let me know if anything is missed out.

Hi Sharath_123
Thanks for your post
I get an error: "Operation must use an updateable query."
I use a TADOQuery
Is the syntax compatible with MS Access? or am I doing something wrong?

Execute the SELECT statement only. Don't execute the UPDATE statement. Basically i want to know if there is any error in UPDATE statement. If the SELECT statement is working fine, then we can look into the UPDATE statement.

------ This is your table
CREATE TABLE #A(IdNo int,Name nvarchar(10) NULL,ParentNo int NULL,ChildNum int NULL)
INSERT INTO #A VALUES(1,NULL,NULL,NULL)
INSERT INTO #A VALUES(2,NULL,1,NULL)
INSERT INTO #A VALUES(3,NULL,NULL,NULL)
INSERT INTO #A VALUES(4,NULL,1,NULL)
INSERT INTO #A VALUES(5,NULL,NULL,NULL)
 
------ The data in your table
SELECT * FROM #A
/*
IdNo	Name	ParentNo	ChildNum
1	NULL	NULL	NULL
2	NULL	1	NULL
3	NULL	NULL	NULL
4	NULL	1	NULL
5	NULL	NULL	NULL
*/
------ This SELECT statement gives what are the records to be updated and the count of ParentNo
SELECT * FROM #A A JOIN (
SELECT ParentNo,COUNT(ParentNo) Cnt_ParentNo FROM #A GROUP BY ParentNo) B
    ON A.IdNo = B.ParentNo
/*
IdNo	Name	ParentNo	ChildNum	ParentNo	Cnt_ParentNo
1	NULL	NULL	NULL	1	2
*/
 
  DROP TABLE #A
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

@Sharath_123

As I wrote earlier, the actual table has over 6 millions records where some of the records have childs records and some have none. Meanning that some of the ParentNo values are NULL.

the data sample you provided must be accurate for people to understand what you want

you need to supply it the way you want, without adding comments with needed changes.

@Geert_Gruwez
What do you mean by "data sample" ?

I asked you to execute this query on your table. Let me know if this doesn't give proper result. Whats the matter if you have 6 million records?

SELECT * FROM YourTable JOIN (
SELECT ParentNo,COUNT(ParentNo) Cnt_ParentNo FROM YourTable GROUP BY ParentNo) B
    ON A.IdNo = B.ParentNo

                                              

1:
2:
3:

@Sharath_123

This works for Sql server NOT for MS Access

SELECT * FROM YourTable A JOIN ( SELECT ParentNo,COUNT(ParentNo) Cnt_ParentNo FROM YourTable GROUP BY ParentNo) B ON A.IdNo = B.ParentNo  

What is the error message you are getting?

"Syntax error in FROM clause."

This is a really nasty query to do in Access.  You're not allowed to use subqueries, which makes it nearly impossible (possibly impossible!)

I can't get a query to work.  Is there any reason why you can't do this programatically?  With 2 ADQuery objects, for example?  You could parse one of them, that is every row in the table, and set the ChildNum value to the result of a select count, that you ran in another query.

I don't generally use Access, because of the problems it has with complex queries, so that's how I'd do it, unless of course you have a reason not to?

Incidentally, here's the query that Sharath was asking you to run.  It gives you each IdNo and what the ChildNum value should be set to...

SELECT T1.IdNo, COUNT(T2.IdNo) AS ChildNum FROM MyTable T1 LEFT JOIN MyTable T2 ON T2.ParentNo = T1.IdNo GROUP BY T1.IdNo

I've tried LOTS of ways to convert it to an update statement that will run in Access, but none of them work.

@Jaymol

Looks like Access can't handle complex queries, I could get it work on Sql server but no with Access, so that is the reason I've posted this question.

Sure I can achieve that programatically, but it will take a very long long time considering the table size.

I'll give a couple of days to let experts to come up with a solution, if they can't, I'll just convert the Access table to Sql, run the update query and export it back to Access. It will take much less time that doing it programatically.

Thanks for your post.

Well if you're just doing it the once (or not needing to do it that often), you can use Query Analyzer to update the table, so you CAN use a real query.

I don't know why, but the attached code won't run in one go, so just run each line on its own.

Let us know if you have any problems.  I just ran it and it worked for me.

Good luck!

John

sp_addlinkedserver 'AccessDB', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'c:\temp\test.mdb' -- obviously change this to suit
 
sp_addlinkedsrvlogin 'AccessDB', false
 
-- change MyTable to your table name
UPDATE AccessDB...MyTable SET ChildNum = (
	SELECT B.Cnt_ParentNo FROM (
		SELECT ParentNo, COUNT(ParentNo) Cnt_ParentNo FROM AccessDB...MyTable GROUP BY ParentNo) B
			WHERE IdNo = B.ParentNo)
 
sp_droplinkedsrvlogin 'AccessDB', NULL 
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Works great!
Thank you and happy new year (: