Updation with date criteria - 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

General SQL Server Forums

New to SQL Server Programming

Updation with date criteria

Author

Topic

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-10 : 09:41:08

I convey regards to all the experts.
I am using SQL SERVER as back end and VB6 as front end.
I have 2 tables
one containing purchase records and another is Item master.
I want to update a field named 'totpurchqty' in Item table with all purchase made upto the given date or earlier than given date.

I fire following sql:-(DT is a masked a Masked Edit Control)

update itemmst set totpurchqty=(select isnull(sum(inwqty),0) from inwdetl where inwdetl.itemid=itemmst.itemid
and inwarddate<= & DT.Text)

No records are updated. If I omit the date criteria from above sql it works fine.If I use SELECT type query using date criteria it works. Please help me experts, what am I missing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 09:43:33

you want to update all records in totpurchqty with same value? thats whats above query does. also whats the datatype of inwarddate?

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-11 : 09:11:09

Thanks visakh16 for reply.
Inwardate is of DateTime type. Regional setting in my computer is DD/MM/YYYY. I have tried using format(dt.text,"dd/mm/yyyy") too in place of DT.Text. but no result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:25:36

you didnt answer my first question. is your intention to populate all rows of totpurchqty with same value?

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-12 : 08:12:04

Sorry for in-convenience.
I want to update totpurchqty with total purchase qty. of related item (purchase qty. stored in inwdetl table).
E.g.
InwDetl Table containing ItemMst table
XYZ 100 XYZ 400 (Totpurchqty)
ABC 200 ABC 700 (Totpurchqty)
XYZ 300
ABC 500

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 09:30:05

[code]UPDATE t
SET t.totpurchqty=tmp.Total
FROM itemmst t
INNER JOIN (SELECT itemid,sum(inwqty) AS Total
FROM inwdetl
GROUP BY itemid)tmp
ON tmp.itemid=t.itemid
[/code]

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-18 : 09:14:57

visakh16 Sir
Sorry to say but date criteria is missing in your above query.My question is related to date comparision, without date comparsion it works fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:17:05

[code]
UPDATE t
SET t.totpurchqty=tmp.Total
FROM itemmst t
INNER JOIN (SELECT itemid,sum(inwqty) AS Total
FROM inwdetl
WHERE inwarddate<= & DT.Text
GROUP BY itemid)tmp
ON tmp.itemid=t.itemid
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources