Rewritten UPDATE query - is it the same?

[teddysnips]

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.TransFromID =
tblREFUNDS.BookingID SET tblREFUNDS.[MOVED TO NEW BKREF] =
tblbookings.bookingid
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.bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.TransFromID) Is Not Null)) AND (tblREFUNDS.BookingID =
tblbookings.bookingid);

UPDATE tblREFUNDS AS R SET R.[MOVED TO NEW BKREF] = (SELECT
tblbookings.bookingid from tblbookings where
tblbookings.TransFromID = R.BookingID AND tblbookings.TransFromID 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]

On Tue, 29 Apr 2008 03:25:51 -0700 (PDT), te********@hotmail.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.TransFromID =
tblREFUNDS.BookingID SET tblREFUNDS.[MOVED TO NEW BKREF] =
tblbookings.bookingid
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.bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.TransFromID) Is Not Null)) AND (tblREFUNDS.BookingID =
tblbookings.bookingid);

UPDATE tblREFUNDS AS R SET R.[MOVED TO NEW BKREF] = (SELECT
tblbookings.bookingid from tblbookings where
tblbookings.TransFromID = R.BookingID AND tblbookings.TransFromID 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]

On 29 Apr, 15:42, Tom van Stiphout <[e-mail]:

On Tue, 29 Apr 2008 03:25:51 -0700 (PDT), [e-mail]
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