Update Query - Multiple SETS and WHERE conditions

[jasonbarnes]

I am having trouble getting this query to find records to update. I know there are matches because this was previously done in Excel.


UPDATE tblTestData, tblBusinessRules1

SET tblTestData.DESTINATIONSTATE = tblBusinessRules1.DESTINATIONSTATE
AND tblTestData.DESTINATIONCITY=tblBusinessRules1.DEST INATIONCITY
AND tblTestData.[#BUSRULE]=1

WHERE ((tblTestData.NAMEFUNCTION=tblBusinessRules1.NAMEF UNCTION)


And (tblTestData.CITY=tblBusinessRules1.CITY)
And (tblTestData.ST=tblBusinessRules1.ST)
And (tblTestData.COM4_DESC=tblBusinessRules1.COM4_DESC )
And (tblTestData.USPORT=tblBusinessRules1.USPORT)
And (tblTestData.FPORT=tblBusinessRules1.FPORT)
And (tblTestData.ULTPORT=tblBusinessRules1.ULTPORT)
And (tblTestData.SLINE=tblBusinessRules1.SLINE));

[weekend00]

use comma instead of and for SET clause:
SET tblTestData.DESTINATIONSTATE = tblBusinessRules1.DESTINATIONSTATE,
tblTestData.DESTINATIONCITY=tblBusinessRules1.DEST INATIONCITY, tblTestData.[#BUSRULE]=1

[jasonbarnes]

I noticed that after I posted and it still doesn't work

[weekend00]

what's wrong then?
I found some space where there should not be, I am not sure if it's you typo when posting the thread:

UPDATE tblTestData, tblBusinessRules1

SET tblTestData.DESTINATIONSTATE = tblBusinessRules1.DESTINATIONSTATE
AND tblTestData.DESTINATIONCITY=tblBusinessRules1.DEST INATIONCITY
AND tblTestData.[#BUSRULE]=1

WHERE ((tblTestData.NAMEFUNCTION=tblBusinessRules1.NAMEF UNCTION)
And (tblTestData.CITY=tblBusinessRules1.CITY)
And (tblTestData.ST=tblBusinessRules1.ST)
And (tblTestData.COM4_DESC=tblBusinessRules1.COM4_DESC )
And (tblTestData.USPORT=tblBusinessRules1.USPORT)
And (tblTestData.FPORT=tblBusinessRules1.FPORT)
And (tblTestData.ULTPORT=tblBusinessRules1.ULTPORT)
And (tblTestData.SLINE=tblBusinessRules1.SLINE));

[jasonbarnes]

Must be a typo in the pasting.

Here is where it stands now.

UPDATE tblTestData, tblBusinessRules1 SET tblTestData.DESTINATIONSTATE = tblBusinessRules1.DESTINATIONSTATE, tblTestData.DESTINATIONCITY = tblBusinessRules1.DESTINATIONCITY, tblTestData.[#BUSRULE] = 1
WHERE ((tblTestData.NAMEFUNCTION=tblBusinessRules1.NAMEF UNCTION) And (tblTestData.CITY=tblBusinessRules1.CITY) And (tblTestData.ST=tblBusinessRules1.ST) And (tblTestData.COM4_DESC=tblBusinessRules1.COM4_DESC ) And (tblTestData.USPORT=tblBusinessRules1.USPORT) And (tblTestData.FPORT=tblBusinessRules1.FPORT) And (tblTestData.ULTPORT=tblBusinessRules1.ULTPORT) And (tblTestData.SLINE=tblBusinessRules1.SLINE));

[weekend00]

what error did you get?

[jasonbarnes]

No error. I wish I did and maybe I could figure it out.

It just says, "you are about to update 0 rows".

And I know this isn't the case because out of the 19000 rows. Roughly 1200 should be updated based on the conditions in the BusinessRules1 table.

[weekend00]

it must be because you criteria logic is not correct.

[jasonbarnes]

What do you mean by that? There must be a type mismatch in the WHERE conditions? I based this UPDATE off another simple one that worked. Also I haven't touched the field types in the database yet as its a fairly new one from migrated data. All the data was strictly imported as text fields.

Here is my other update query that worked.

UPDATE tblTestData, tblNamesTable SET tblTestData.NAMEFUNCTION = [tblNamesTable].CORRECTEDNAME
WHERE ((([tblTestData].NAME)=[tblNamesTable].AKANAME));

I've played with added [s in the current one too to no avail.

If you PM me your email address I can probably email you the file if that would help. Thanks for your help. I see that you've been the one responding to most of the Update questions.

[weekend00]

Please make sure there is no NULL values in these fields:
((tblTestData.NAMEFUNCTION=tblBusinessRules1.NAMEF UNCTION) And (tblTestData.CITY=tblBusinessRules1.CITY) And (tblTestData.ST=tblBusinessRules1.ST) And (tblTestData.COM4_DESC=tblBusinessRules1.COM4_DESC ) And (tblTestData.USPORT=tblBusinessRules1.USPORT) And (tblTestData.FPORT=tblBusinessRules1.FPORT) And (tblTestData.ULTPORT=tblBusinessRules1.ULTPORT) And (tblTestData.SLINE=tblBusinessRules1.SLINE));

as you may know, null=null is not true.

[jasonbarnes]

There are over 1300 business rules that when the conditions match from the WHERE clause, the CITY and STATE are written from the BusinessRules1 table to the TestData one.

Out of all these I know one condition is met and should be "updated"

I mean you can "update" a NULL field right.

[weekend00]

I mean that there is no record match your where criteria.

all of 8 fields need to be matched( and no null), then record will be updated.

are you sure the one which you think it should be updated has no null in any of these 8 fields in either table?

[jasonbarnes]

Positive.

I can show you record #1 in the BusinessRules1 has a match at record #31 in the TestData table.

[weekend00]

Show me please.

[jasonbarnes]

Gotta run to lunch. I'm having trouble uploading but I'll get back to it when I get back. Can you PM your email and make it easier when we get back?