SQL: Updating via joins

Report · Jan 18, 2007

I need to update some records in a table based on a query using two joins.

The specific records I need to delete are determined via the query:

SELECT WeFAQs.collectionID, WeFAQs.question, WeFAQs.categoryID,
WeFAQs.publicAccess, WeFAQs.sort
FROM WeFAQs LEFT OUTER JOIN
We_about_categories CAT ON CAT.categoryID =
WeFAQs.categoryID LEFT OUTER JOIN
We_about_categories PARENTCAT ON PARENTCAT.categoryID
= CAT.categoryParentID
WHERE (WeFAQs.categoryID = 9) OR
(categoryParentID = 9)

I only need to update the 5 fields selected in the WeFAQs table.

So, my question...can I do this with a SELECT INTO or an UPDATE command? The
syntax is eluding me.

Or is this a prime example of where a temp table is useful?

-Darrel

Report · Jan 18, 2007

It appears you're simply selecting all records from WeFAQs that are in
category 9 or a subcategory of category 9. That doesn't really require two
joins on the categories table.

Anyway, you'll need a subquery. Either IN or EXISTS will work; just see
which one's faster on your particular tables:

UPDATE WeFAQs
SET CollectionID=x, etc...
WHERE CategoryID IN (SELECT CategoryID FROM We_about_categories WHERE
CategoryID=9 OR CategoryParentID=9)

UPDATE WeFAQs
SET CollectionID=x, etc...
WHERE EXISTS(SELECT * FROM We_about_categories WHERE
CategoryID=WeFAQs.CategoryID AND (CategoryID=9 OR ParentCategoryID=9))

I think the IN version is easier to understand this time around and better
describes what you're actually trying to do.

Report · Jan 18, 2007

You can have syntax like

update a set a.f=b.f
from a inner join b on a.id=b.id

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

Report · Jan 19, 2007

> It appears you're simply selecting all records from WeFAQs that are in
> category 9 or a subcategory of category 9. That doesn't really require
> two joins on the categories table.

Umm...yea, I think you're right. I just need an OR statement. Duh. ;o)

> Anyway, you'll need a subquery. Either IN or EXISTS will work; just see
> which one's faster on your particular tables:
>
> UPDATE WeFAQs
> SET CollectionID=x, etc...
> WHERE CategoryID IN (SELECT CategoryID FROM We_about_categories WHERE
> CategoryID=9 OR CategoryParentID=9)

It just occured to me that I'm updating multiple records with different
data. I can't do that with one query, can I? I have to loop through each
record and update it individually, correct?

-Darrel

Report · Jan 19, 2007

"darrel" <notreal@nowhere.com> wrote in message
news:eoqngd$aae$1@forums.macromedia.com...
> It just occured to me that I'm updating multiple records with different
> data. I can't do that with one query, can I? I have to loop through each
> record and update it individually, correct?
>

Not necessarily. If the updated data depends on the current data, then you
can issue a series of statements, one per update class, or use a CASE:
UPDATE Table
SET someCol = CASE otherCol WHEN x THEN a WHEN y THEN b END
WHERE otherCol IN (x, y)

If the updated data comes from another table, you get to use SQL Server's
proprietary UPDATE ... FROM syntax.

UPDATE A
SET A.Col=B.Col
FROM TableA A
INNER JOIN TableB B ON A.ID=B.ID
WHERE A.Filter='value'

The WHERE isn't strictly necessary; rows left out by the INNER JOIN will not
be updated. You'll also notice that I've apparently issued the update
against the table alias - that's important. SQL Server needs to be really
sure what you're trying to update, and for all it knows, you'll need two
copies of the table with different aliases. You can choose to use no
aliases at all, if that better fits your style.

There are other ways to write UPDATE ... FROM, but I think the above is the
easiest to read and understand. Purists will demand correlated subqueries:
UPDATE TableA
SET Col = (SELECT Col FROM TableB WHERE TableB.ID=TableA.ID)

But that takes forever to write (especially for multiple columns) and
performance is often bad, at least with the way SQL Server's optimizer
handles the query.

Report · Jan 19, 2007

> UPDATE A
> SET A.Col=B.Col
> FROM TableA A
> INNER JOIN TableB B ON A.ID=B.ID
> WHERE A.Filter='value'

So, to clarify...my INNER JOIN is where I'd be grabbing the distinct subset
of records from Table B. And that, in turn, would update the individual
records in Table A that match each ID field.

That's pretty slick!

Thanks, as always, Lionstone!

-Darrel

Report · Jan 19, 2007

"darrel" <notreal@nowhere.com> wrote in message
news:eoqqk6$dmj$1@forums.macromedia.com...
> So, to clarify...my INNER JOIN is where I'd be grabbing the distinct
> subset of records from Table B. And that, in turn, would update the
> individual records in Table A that match each ID field.
>
> That's pretty slick!
>
> Thanks, as always, Lionstone!
>

You got it.

Report · Jan 19, 2007

>> So, to clarify...my INNER JOIN is where I'd be grabbing the distinct
>> subset of records from Table B. And that, in turn, would update the
>> individual records in Table A that match each ID field.
>>
>> That's pretty slick!
>>
>> Thanks, as always, Lionstone!

Well, one more question. Here's what my query now looks like:

UPDATE PUBLICFAQS
SET PUBLICFAQS.collectionID = STAGING.collectionID,
PUBLICFAQS.question = STAGING.question, PUBLICFAQS.categoryID =
STAGING.categoryID,
PUBLICFAQS.publicAccess = STAGING.publicAccess,
PUBLICFAQS.sort = STAGING.sort
FROM [publicDBserver].DBPub.Pub.WeFaqs PUBLICFAQS INNER JOIN
WeFaqs STAGING ON PUBLICFAQS.collectionID =
STAGING.collectionID INNER JOIN
We_about_categories CAT ON CAT.categoryID =
STAGING.categoryID
WHERE (CAT.categoryID = 14) OR
(CAT.categoryParentID = 14)

But I end up with this error:

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.]
---------------------------
OK Help
---------------------------

Any idea what might be causing that? Google seems to indicate it's a
datatype issue, but even if I go through the fields and just try updating it
field by field, I get the same error.

-Darrel

Report · Jan 22, 2007

"darrel" <notreal@nowhere.com> wrote in message
news:eordm0$6d2$1@forums.macromedia.com...
> Any idea what might be causing that? Google seems to indicate it's a
> datatype issue, but even if I go through the fields and just try updating
> it field by field, I get the same error.
>

From the looks of your query, the target table is on a remote (linked)
server. If that's the case, then the table being updated must have a unique
index.

Report · Jan 22, 2007

> From the looks of your query, the target table is on a remote (linked)
> server. If that's the case, then the table being updated must have a
> unique index.

What, exactly, does 'unique index' mean?

-Darrel

Report · Jan 22, 2007

"darrel" <notreal@nowhere.com> wrote in message
news:ep2ijp$i9s$1@forums.macromedia.com...
> What, exactly, does 'unique index' mean?
>
> -Darrel

An index with the "unique" property, meaning an index on either the primary
key (which is automatically created when you create a primary key on your
table) or any candidate key. It can be a clustered or non-clustered index,
and it can be a single column or a combination of many columns. But the
column(s) must be able to uniquely identify the row.

If your table has an identity or ROWGUID column but you didn't also declare
that column to be the primary key, then that doesn't count. You have to
explicity set the primary key even then (though in the case of ROWGUID, it's
probably a good idea to create the key as a nonclustered index instead of
the default clustered index).

Report · Jan 22, 2007

> An index with the "unique" property, meaning an index on either the
> primary key (which is automatically created when you create a primary key
> on your table) or any candidate key. It can be a clustered or
> non-clustered index, and it can be a single column or a combination of
> many columns. But the column(s) must be able to uniquely identify the
> row.
>
> If your table has an identity or ROWGUID column but you didn't also
> declare that column to be the primary key, then that doesn't count. You
> have to explicity set the primary key even then (though in the case of
> ROWGUID, it's probably a good idea to create the key as a nonclustered
> index instead of the default clustered index).

Ah. Right now, the two tables mirror each other. The staging one has a
primary key field. The live table simply mirrors that field, but it isn't
set as primary key.

It ACTS as a primary key, since it's a copy of the table that does use them
as primary keys, though.

So, in this case, should I just set an extra pimrary key field that isn't
really used for anything other than this specific query?

Or am I overthinking this? Maybe I can just set the ID field on the live
site to be a unique ID even though it's just a copy of the unique ID on the
internal table...

-Darrel

Report · Jan 22, 2007

"darrel" <notreal@nowhere.com> wrote in message
news:ep2jvr$k0t$1@forums.macromedia.com...
> It ACTS as a primary key, since it's a copy of the table that does use
> them as primary keys, though.

>Or am I overthinking this? Maybe I can just set the ID field on the live
>site to be a unique ID even though it's just a copy of the unique ID on the
>internal table...

You're over-thinking. Other tables have no bearing on the uniqueness of a
row in a table. Declare the ID column the primary key of your production
table and your problem is solved. If it acts like a primary key and you
have no other primary key, then it *is* the primary key and should be
declared as such. As an added bonus, your query performance will skyrocket
on any queries which join on the ID column.