Rewritten UPDATE query - is it the same?

[teddysnips@hotmail.com]

My client has moved their back-end database from Access to SQL Server,
and now the following query doesn't work (Operation must use an
updateable query):

UPDATE tblbookings INNER JOIN tblREFUNDS ON tblbookings.Tra nsFromID =
tblREFUNDS.Book ingID SET tblREFUNDS.[MOVED TO NEW BKREF] =
tblbookings.boo kingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null));

Both the following variations seem to work, but I can't decide if they
are syntactically interchangeable :

UPDATE tblRefunds, tblBookings
SET [MOVED TO NEW BKREF] = tblbookings.boo kingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null)) AND (tblREFUNDS.Boo kingID =
tblbookings.boo kingid);

UPDATE tblREFUNDS AS R SET R.[MOVED TO NEW BKREF] = (SELECT
tblbookings.boo kingid from tblbookings where
tblbookings.Tra nsFromID = R.BookingID AND tblbookings.Tra nsFromID IS
Not Null)
WHERE (((R.[MOVED TO NEW BKREF])=0));

I can't really test this because it's live data, and time is
incredibly tight, so you have my copious thanks in advance.

Edward

[Tom van Stiphout]

Re: Rewritten UPDATE query - is it the same?

On Tue, 29 Apr 2008 03:25:51 -0700 (PDT), teddysnips@hotm ail.com
wrote:

Backup the database (or take the backup from last night). Restore it
as <dbname>_Beta and perform your tests there. You can spare the 5
minutes.
My gut feeling: both are interchangeable .

-Tom.

>My client has moved their back-end database from Access to SQL Server,
>and now the following query doesn't work (Operation must use an
>updateable query):
>
>UPDATE tblbookings INNER JOIN tblREFUNDS ON tblbookings.Tra nsFromID =
>tblREFUNDS.Boo kingID SET tblREFUNDS.[MOVED TO NEW BKREF] =
>tblbookings.bo okingid
>WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
>((tblbookings. TransFromID) Is Not Null));
>
>Both the following variations seem to work, but I can't decide if they
>are syntactically interchangeable :
>
>UPDATE tblRefunds, tblBookings
>SET [MOVED TO NEW BKREF] = tblbookings.boo kingid
>WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
>((tblbookings. TransFromID) Is Not Null)) AND (tblREFUNDS.Boo kingID =
>tblbookings.bo okingid);
>
>UPDATE tblREFUNDS AS R SET R.[MOVED TO NEW BKREF] = (SELECT
>tblbookings.bo okingid from tblbookings where
>tblbookings.Tr ansFromID = R.BookingID AND tblbookings.Tra nsFromID IS
>Not Null)
>WHERE (((R.[MOVED TO NEW BKREF])=0));
>
>I can't really test this because it's live data, and time is
>incredibly tight, so you have my copious thanks in advance.
>
>Edward

[teddysnips@hotmail.com]

Re: Rewritten UPDATE query - is it the same?

On 29 Apr, 15:42, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:

On Tue, 29 Apr 2008 03:25:51 -0700 (PDT), teddysn...@hotm ail.com
wrote:
>
Backup the database (or take the backup from last night). Restore it
as <dbname>_Beta and perform your tests there. You can spare the 5
minutes.
My gut feeling: both are interchangeable .
>
-Tom.

Thanks. Will do.

Edward