May 23, 2008 at 2:18 am
Hi!
I'm running the following query:
UPDATE T1
SET nameField = 'XXXX'
FROM ordercard T1
JOIN ordernew T2
ON T1.[receipt] = T2.receiptnr
AND active = 'N'
and it affects 10649 rows.
Then I do:
SELECT *
FROM ordercard T1
JOIN ordernew T2
ON T1.[receipt] = T2.receiptnr
AND active = 'N'
and it returns 10827 rows. Why is there a difference in row count?
Next I try:
SELECT *
FROM ordercard, ordernew
WHERE active = 'Y'
and it returns 11255 rows where ALL have nameField = 'XXXX'
however where in my query did I tell the nameField to become XXXX for those with active='Y' ???
Please help, this is getting on my nervs. I think the problem lies with the joins. I tried making a VBscript (yeah the site is so old so it runs VBSCRIPT lol) but it times out as there's too many rows. Maybe u can help optimizing it?
Pseudocode in a totally made up language (based more on asp.net C#)
query = "SELECT * FROM ordernew WHERE ordernew.active = 'N'"
reader = getreader(query)
foreach result in reader
{
query2 = "UPDATE ordercard SET nameField='XXXX' WHERE receipt='" + result["receiptnr"] + "'"
execQ(query2)
}
// times out but does pretty many queries before it does
May 23, 2008 at 2:53 am
check the duplicates in T2.
Some ids / values which you are using for join may have repetition in T2
May 23, 2008 at 3:13 am
yes there are duplicates, or atleast so I've heard. (I'm not the one who made this stuff in the first place)
What am I supposed to do then?
May 23, 2008 at 11:11 pm
try like this
if active is from t1
SELECT *
FROM ordercard T1 where receipt in (select receiptnr from ordernew)
AND active = 'N'
if active is from t2
SELECT *
FROM ordercard T1 where receipt in (select receiptnr from ordernew where active = 'N')
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply