sql server new users: help with update query? -- sql server new users

help with update query?

r
3/9/2006 1:03:32 PM

sql server new users:

I have to tables, Dealers and Data. I'm sure this seems redundant without
knowing the details, but I need it to be done and can't figure it out.

Each dealer in the Dealers table has 2 IDs, D_ID (primary ID) and S_ID
(secondary ID).
The Data table has multiple records for each dealer, and the ID field for
each record can contain either the D or S ID (primary or secondary).

The Data table has a "usable ID" column which needs to be updated with the
PRIMARY ID (the D_ID) for the associated dealer. Therefore, here is what I
need to do, but I can't seem to get the syntax right.

I need to run a query that will put the D_ID (from Dealers) in the UsableID
column of the Data table IF the Dealer.D_ID is equal to the Data.ID for that
record.

This is what it looked like in Access, but the syntax isn't working in SQL:

UPDATE Dealers INNER JOIN Data ON Dealers.D_ID = Data.ID SET Data.UsableID =
[dealers].[D_ID];

I've tried a few things in SQL but can't seem to get it right.

Can someone help?

Re: help with update query?

Hugo Kornelis
3/9/2006 10:32:21 PM

[quoted text, click to view]

Hi r,

Equivalent T-SQL syntax:

UPDATE Data
SET UsableID = Dealers.D_ID
FROM Data
INNER JOIN Dealers
ON Dealers.D_ID = Data.ID

And if you prefer portability, here's the same query in ANSI-compliant
syntax, that should work on any decent RDBMS:

UPDATE Data
SET UsableID = Data.ID
WHERE EXISTS
(SELECT *
FROM Dealers
WHERE Dealers.D_ID = Data.ID)

(both queries above untested - see www.aspfaq.com/5006 if you prefer a
tested reply)

--