SQL Syntax

Question

Post reply

SQL Syntax

Joan Shroyer-Keno

Old Hand

Points: 381
More actions
August 17, 2004 at 10:30 am

#161996

I wrote a qry in ACCESS and know the syntax is not parsing. I am not sure what the syntax difference is...
I would very much appreciate any help.

UPDATE DTSinvvendor
SET manufacturer = DTS_MRP_DRAWING.NO_CODE_IDENT
WHERE DTSinvvendor INNER JOIN
DTS_ubmf01_part_master ON DTSinvvendor.itemnum = DTS_ubmf01_part_master.PART_NO_001 LEFT OUTER JOIN
DTS_MRP_DRAWING ON DTS_ubmf01_part_master.DBKEY_8000 = DTS_MRP_DRAWING.DBKEY

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

UPDATE DI SET manufacturer = DTS_MRP_DRAWING.NO_CODE_IDENT

FROM DTSinvvendor DI

INNER JOIN DTS_ubmf01_part_master DM ON DI.itemnum = DM.PART_NO_001

LEFT JOIN DTS_MRP_DRAWING DD ON DM.DBKEY_8000 = DD.DBKEY

The problem was the WHERE DTSinvvendor INNER JOIN. I just created aliases to make it easier to read

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Joan Shroyer-Keno Old Hand Points: 381 More actions · Answer 2

This returns an error of "The query designer does not support the optional FROM clause construct."

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

what are you using to develop the SQL query?????

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Vladan SSC-Insane Points: 22072 More actions · Answer 4

AJ,

shouldn't the SET command work with the alias as well (DD instead of DTS_MRP_DRAWING) ? Maybe that's the source of error, though I don't remember seeing such error message.

cheers, Vladan

Joan Shroyer-Keno Old Hand Points: 381 More actions · Answer 5

Thanks for everyone's replies.

I'm working in DTS query Designer. The Execute SQL task in specific.

I can get the following statement to execute, but cannot chagne it into an update statement

SELECT DTSinvvendor.itemnum, DTSinvvendor.manufacturer, DTS_MRP_DRAWING.NO_CODE_IDENT

FROM DTSinvvendor INNER JOIN

DTS_ubmf01_part_master ON DTSinvvendor.itemnum = DTS_ubmf01_part_master.PART_NO_001 INNER JOIN

DTS_MRP_DRAWING ON DTS_ubmf01_part_master.DBKEY_8000 = DTS_MRP_DRAWING.DBKEY

Regards

Luigi Visintin SSC Eights! Points: 822 More actions · Answer 6

Maybe you can try something like:

UPDATE DTSinvvendor

SET DTSinvvendor.manufacturer = DM.NO_CODE_IDENT

FROM DTSinvvendor DI

INNER JOIN DTS_ubmf01_part_master DM ON DI.itemnum = DM.PART_NO_001

LEFT JOIN DTS_MRP_DRAWING DD ON DM.DBKEY_8000 = DD.DBKEY

the problem here is that the 'update table name' should not be confused with the tables names in the FROM clause.

HTH

Luigi