By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

?HLEP..Sql Server UPDATE INNER JOIN QUERY ?????..

P: n/a
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....

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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)

Jul 23 '05 #2

P: n/a
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)

Jul 23 '05 #3

P: n/a
(me*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

P: n/a
I don't think that you can write something like this:
SELECT HARMFULEVENT FROM HARMFULEVENT

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.