Convert Access Update Code to Sql 2k. 1

[nobeltlp]

This works in VB MSACCESS But I can not seem to correctly
convert to SQL 2000.

UPDATE tblCmsA 
INNER JOIN tblCmsB ON tblCmsA.ACNo = tblCmsB.ACNo 
SET tblCmsA.ACNo = tblCmsB.ACNo], tblCmsA.BCNo = tblCmsB.BCNo, tblCmsB.flag = '1', tblCmsA.Flag = 1;"

 

[gmmastros]

With SQL Server, the Set comes before the tables. You also need to add a from when you are joining multiple tables in an update. As you can see, the syntax is similar, but not identical. You'll get the hang of it.

Unfortunately, you can only update data in 1 table at a time, so you should write a Stored Procecure.

Also, It doesn't make sense to Update the ACno field since you are linking those fields together.

UPDATE tblCmsA 
SET    tblCmsA.BCNo = tblCmsB.BCNo, 
       tblCmsA.Flag = 1
From   tblCmsA
       INNER JOIN tblCmsB ON tblCmsA.ACNo = tblCmsB.ACNo 

UPDATE tblCmsB
Set    tblCmsB.flag = '1', 
From   tblCmsA
       INNER JOIN tblCmsB ON tblCmsA.ACNo = tblCmsB.ACNo

*** disclaimer
As with any update or delete, please backup the database first. I don't want to feel responsible for any adverse actions this may cause.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Also,

tblCmsB.flag = '1', tblCmsA.Flag = 1;"

In the first part of that you are setting tblCmsB.flag to the character 1 (not integer 1). Is tblCmsB.flag of CHAR or VARCHAR datatype?

In the last part of that, you are setting tblCmsA.Flag to 1;". I don't believe that's what your intention is.

TSQL (MS SQL Server's language) doesn't require the ; to run the code and the " is not needed.

If the 1's are integers, don't put single quotes around them. If they are characters, then use the single quotes.

-SQLBill

Posting advice: FAQ481-4875