Hi all,

I have the following query that returns a two column result set.

SELECT
    s.standard_id, si.standard_item_id
FROM
    standards s
INNER JOIN
    standard_items_bk si
ON
    s.parent_id = si.standard_id
AND
    s.idx = si.idx

Sample results:

standard_id    |    standard_item_id
100                     1
101                     2

What i need to do is used the details returned from this query to construct
an update query like so:

UPDATE standards SET standard_id = s.standard_id WHERE standard_item_id =
si.standard_item_id //on a row per row basis

Is this possible to TSQL?

I am not really sure how to approach this, so any help would be
appreciated!!!!

Cheers,
Adam

Huh?  You want to set standard_id to itself where standard_item_id is
itself?  That doesn't make sense.  Maybe I'm just not reading it right.

Are you asking if it's possible to use JOINs in UPDATE statements?  If
so, then the answer is yes.

UPDATE <tablename>
SET <colx> = ...
FROM <table1>
    [INNER | LEFT | RIGHT] JOIN <table2> ON ...
    etc., etc.
WHERE ...
(highly abridged syntax description)

I'm guessing you're trying to do something like

UPDATE standards
SET standard_id = p.standard_id
FROM standards s
    INNER JOIN standard_item_id p on p.standard_id = s.parent_id
WHERE p.SomeOtherCol = 'blah, blah, blah'

But I'm unclear on exactly what you're trying to achieve.  If you want
the exact syntax as an answer then, in addition to describing accurately
what you're trying to achieve, you'll need to post some schema (i.e.
DDL) for the tables involved, some sample data and expected results.

Show quoteHide quote

Optimizing an IN clause
SQL 2k5 vs 2k - different results on same execution? ORDER BY prob
What is this called, and how to do it?
SQL Server vs the Competition
Query Problem