SQL 'UPDATE' Statement Question

[rudvs2]

Hey all.

I working with a sql database. I have to Tables that contain data on a customer. I am using a left outer join query to retrieve and view a complete data set on the customer. My question is when Iam modifying customer details in some instances i need to update information in both tables. Is there a syntax for doing this at the same time eg

UPDATE MYTABLE LEFT OUTER JOIN MYOTHERTABLE SET BLAH, BLAH WHERE BLAHBLAH = 'BLEH'

would a statement like that work?

I have tried it and get errors I cant seem to find any documentation on doing joined table updates

any help would be appreciated thanks

[Beacon]

No i dont think it will work.

Look into:

Unique Table and Resync commands.

e.g:
rs.Properties("Unique Table") =
rs.Properties("Resync Command") =

then you should be able to use rs.update i think.

good luck
b

[Killazzz]

I've just tried this in an access database and it works.

UPDATE tabel1 LEFT JOIN tabel2 ON tabel1.ID = tabel2.ID SET tabel1.a = "z";

As you can see you have to specify on what field to join and specify which table.field you update.

[vb_dba]

I use a statement similar to the following for SQL Server:

Update myTable
Set Field1 = 'Test'
From myTable Inner Join yourTable on myTable.ID = yourTable.ID

[Lord_Rat]

I usually use begintrans and committrans, with two updates in between them.

That way, if one fails, no data is changed.