HLEP..Sql Server UPDATE INNER JOIN QUERY ..

Im using an ADP to connect to a SQL Sqever DB.
In access it was really easy to say

Inner join on table1 and table2 and update columnA from table1 with
columnC from table2 where table1.key = table2.key and table2 columnB =
1 and table2 columnD = 4

I have tried all manner of beasts to get this thing to work..

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))

This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0' part which is really important

Any Help would be great...This way looks like it should work also but I get an error 'ADO Error:
HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
can only show one table in an update qurey?

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0))
WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)|||SAME ADO ERROR WHEN I USE THIS ???

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
FROM GIS_EVENTS_TEMP G INNER JOIN
HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
WHERE (H.UNITID = 1 AND H.LISTORDER = 0)|||(meyvn77@.yahoo.com) writes:
> Im using an ADP to connect to a SQL Sqever DB.
> In access it was really easy to say
> Inner join on table1 and table2 and update columnA from table1 with
> columnC from table2 where table1.key = table2.key and table2 columnB =
> 1 and table2 columnD = 4
>
> I have tried all manner of beasts to get this thing to work..
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
> AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))
> This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0' part which is really important
> Any Help would be great...

Unfortunately, it's not very easy to help if we don't know what
tables you have. The standard recommendation for this type of
problem is to post:

o CREATE TABLE statements of the tables nvolved. (Preferrably
cut down to the columns relevant to the problem.(
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the business problem.

The two first points makes it easy to copy and paste into Query Analyzer,
so a tested solution can be developed. The third point makes it possible
to verify that the solution is correct. And the fourth point gives some
extra information which helps to understand the general problem.

> This way looks like it should work also but I get an error 'ADO Error:
> HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
> can only show one table in an update qurey?
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0))
> WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)

No, but because you are referring to HARMFULEVENT outside the subquery.

> AME ADO ERROR WHEN I USE THIS ???
>
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
> FROM GIS_EVENTS_TEMP G INNER JOIN
> HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
> WHERE (H.UNITID = 1 AND H.LISTORDER = 0)

Here you are mixing use of aliases and table name. Once you have
introduced an alias, you can not refer to the full table name in
the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I don't think that you can write something like this:
SELECT HARMFULEVENT FROM HARMFULEVENT