Update query

1 Dec 2005 6:01 PM

Aleks

I have the following task, doing it by hand would take just an increidible
amount of time, perhaps a query can be done to update an entire table ?

This is the scenario.

Table A has a field called "UserId", this will be my source "Id"
Table B has a field called "OlduserID", this is the "Id" that needs to be
replaced

Then I have table C, which has c.Userid, c.OldUserId, this two fields hold
both ID's.

I need to update table B in all rows, so that it checks the first
"OlduserID" against table C, and updates to the value in c.userid, then the
next record and so forth.

Can this be done in a query ?

An example with the above information would be great, then I can change the
names of the tables and fields to what makes sense in my database.

Thanks a lot for any expert advice,

Alejandro

-------------------------------------

I tried it, but gives me an error, this is my final query:

UPDATE CaseComments
SET    CaseId =    (SELECT id
                    FROM   Cases
                    WHERE Cases.tempcaselogid = CaseComments.tempcaselogid)
WHERE EXISTS       (SELECT *
                    FROM   CaseComments
                    WHERE CaseComments.tempcaselogid = Cases.tempcaselogid)

This is the error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Cases' does not match with a table name or alias name
used in the query.

I appreciate it if you could take a look at it.

Aleks

1 Dec 2005 6:43 PM

John Bell

Hi

You exists clause is causing the error message, and I think it may be
unnecessary so try:

UPDATE CaseComments
SET    CaseId =    (SELECT id
                          FROM   Cases
                         WHERE Cases.tempcaselogid =
CaseComments.tempcaselogid)

OR the proprietery form

UPDATE c
SET    CaseId =    s.id
FROM CaseComments c
JOIN Cases s ON s.tempcaselogid = c.tempcaselogid

But that does not seem to be the same as your scenario as I would expect
tempcaselogid to be CaseId.

John

Show quote

"Aleks" wrote:

> I have the following task, doing it by hand would take just an increidible
> amount of time, perhaps a query can be done to update an entire table ?
>
> This is the scenario.
>
> Table A has a field called "UserId", this will be my source "Id"
> Table B has a field called "OlduserID", this is the "Id" that needs to be
> replaced
>
> Then I have table C, which has c.Userid, c.OldUserId, this two fields hold
> both ID's.
>
> I need to update table B in all rows, so that it checks the first
> "OlduserID" against table C, and updates to the value in c.userid, then the
> next record and so forth.
>
> Can this be done in a query ?
>
> An example with the above information would be great, then I can change the
> names of the tables and fields to what makes sense in my database.
>
> Thanks a lot for any expert advice,
>
> Alejandro
>
>
> -------------------------------------
>
>
> I tried it, but gives me an error, this is my final query:
>
>
> UPDATE CaseComments
> SET    CaseId =    (SELECT id
>                     FROM   Cases
>                     WHERE Cases.tempcaselogid = CaseComments.tempcaselogid)
> WHERE EXISTS       (SELECT *
>                     FROM   CaseComments
>                     WHERE CaseComments.tempcaselogid = Cases.tempcaselogid)
>
>
>
> This is the error:
>
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'Cases' does not match with a table name or alias name
> used in the query.
>
>
> I appreciate it if you could take a look at it.
>
> Aleks
>
>
>

1 Dec 2005 10:27 PM

Hugo Kornelis

On Thu, 1 Dec 2005 13:01:10 -0500, Aleks wrote:

>I have the following task, doing it by hand would take just an increidible
>amount of time, perhaps a query can be done to update an entire table ?
(snip)

Hi Aleks,

I already replied to both this question and the followup in another
group (microsoft.public.sqlserver.programming).

Please don't post the same question several times. Please don't post the
same question to different groups.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)