Link to home
Create AccountLog in
Avatar of mpdillon
mpdillon

asked on

MultiTable Update statement

I have two tables in two different databases that can be joined by two fields. I want to update two fields in the one table with a constant. But I only want to update those fields with constants if the record exists in the other table. I am using MS SQL 2005

I have tried

Update P
Set P.Date1=20110101, P.Date2= 20111231
From DB1.dbo.Purchase P
Inner Join DB2.dbo.WS  W
On P.Item_no = W.ItemNo and P.Vend_no = W.VendNo

This did not work. Any suggestions?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

UPDATE DB1.dbo.Purchase
SET Date1 = 20110101, Date2 = 20111231
FROM DB1.dbo.Purchase P
Inner Join DB2.dbo.WS  W
On P.Item_no = W.ItemNo and P.Vend_no = W.VendNo

Open in new window

Try the following:

Update P
Set P.Date1=20110101, P.Date2= 20111231
From DB1.dbo.Purchase P, DB2.dbo.WS  W
WHERE P.Item_no = W.ItemNo AND P.Vend_no = W.VendNo
Try this

UPDATE  P
SET     P.Date1 = 20110101,
        P.Date2 = 20111231
FROM    DB1.dbo.Purchase P
WHERE   EXISTS ( SELECT 1
                 FROM   DB2.dbo.WS W
                 WHERE  P.Item_no = W.ItemNo
                        AND P.Vend_no = W.VendNo )
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mpdillon
mpdillon

ASKER

I accepted this before I could test. My mistake. I read the reply. It made sense and I accepted it. Sorry.

I guess this is more difficult than I thought. I couldn't get any of the solutions by mathewspatrick, s chilkury or indianguru2 to work.

Mathhewspatrick: The update executed but SQL server added a Cross Join and it updated all the records in the table.

s chilkury; I received a message that the two dates could not be updated.

indianguru2: I received a message that the two dates could not be updated. Then SQL added a cross join. The output in SSMS was:
UPDATE    P
SET              P.date1 = 20110101, P.date2 = 20111231
FROM         purchase AS P CROSS JOIN
                      P
WHERE     EXISTS
                          (SELECT     1 AS Expr1
                            FROM         db2.dbo.WS AS W
                            WHERE      (P.item_no = ItemNo) AND (P.vend_no = VendNo))

For anyone reading this thread, I was able to do the update with:

Update Purchase Set Date1=20110101, date2 = 20111231
where Rtrim(Ltrim(Item_no)+Rtrim(Ltrim(Vend_No) in
(Select Rtrim(Ltrim(itemNo))+Rtrim(Ltrim(Vendno)) From DB2.dbo.WS)

BUT I think there has to be a better way. I think the three responses are close but must be missing some little thing.
mpdillon,

If my suggestion did not work, then you should click the 'Request Attention' link and ask the Mods to reopen this question.

Patrick
what is the datatype of date1/2  

set date1='20110101' , date2='20111231'     ?

is it a 1:1 relationship between the tables?
   if so a join should work
otherwise you are probably better off with the correlated exists subquery approach....


does my solution work?
>>For anyone reading this thread, I was able to do the update with:<<
That query is not correct, it is missing paranthesis.  The following may be more efficient:
UPDATE      p
SET      Date1 = 20110101,
      date2 = 20111231
FROM      Purchase p
      INNER JOIN DB2.dbo.WS w ON p.Item_no = w.itemNo AND p.Vend_No = w.Vendno

But first see if the following produces the correct results:
SELECT      p.Item_no, w.itemNo, p.Vend_No, w.Vendno, p.Date, p.date2
FROM      Purchase p
      INNER JOIN DB2.dbo.WS w ON p.Item_no = w.itemNo AND p.Vend_No = w.Vendno
LowfatSpread:
Datatype is bigint, YYYYMMDD. This is an old Db. Evolved from Unix. Used to be YYMMDD until 12/31/1999.

It is a one to one relationship.

Your code worked. But SSMS really modified it.
Here is your code with the actual table and field names. I renamed DB2.
Update data_02.dbo.POItmVnd_SQL2
Set Contract_Frm_Dt=20110101, Contract_Thru_Dt= 20111231
where exists (select itemno from  
 DB2.dbo.WSEOYItemVendorCostUpdate  W
where Data_02.dbo.POItmVnd_SQL2.Item_no = W.ItemNo
and Data_02.dbo.POItmVnd_SQL2.Vend_no = W.VendNo
)
and (Contract_Frm_Dt <> 20110101 or Contract_Thru_Dt <> 20111231)

But when I executed in SSMS, the code morphed to:
UPDATE    POITMVND_SQL2
SET              contract_frm_dt = 20110101, contract_thru_dt = 20111231
WHERE     EXISTS  (SELECT     ItemNo
                  FROM DB2.dbo.WSEOYItemVendorCostUpdate AS W
                  WHERE (POITMVND_SQL2.item_no = ItemNo) AND (POITMVND_SQL2.vend_no = VendNo))
                  AND (contract_frm_dt <> 20110101) OR
                      (contract_thru_dt <> 20111231)
                  AND EXISTS  (SELECT  ItemNo
                              FROM DB2.dbo.WSEOYItemVendorCostUpdate AS W
                              WHERE (POITMVND_SQL2.item_no = ItemNo) AND (POITMVND_SQL2.vend_no = VendNo))

acperkins:
thanks for the note about parens. I removed a lot of file specific information and must have made a mistake.
The select works fine. I had the exact same code working too. However, when i ran the following code in SSMS gives an error.
UPDATE      p
SET      Contract_Frm_dt = 20110101,
      Contract_Thru_dt = 20111231
FROM      POItmVnd_SQL2 p
      INNER JOIN DB2.dbo.WSEOYItemVendorCostUpdate w ON p.Item_no = w.itemNo AND p.Vend_No = w.Vendno

I stored the errors in two jpg. I will try to attach.

 User generated image User generated image
>>But when I executed in SSMS, the code morphed to:<<
And that is because you are using the Design Mode.  That is not a good idea.

>>However, when i ran the following code in SSMS gives an error.<<
The same is true here.  Notice how it added a CROSS JOIN!  

It is also far easier to report errors if you do not use Design mode.  It is a simple copy and paste from the results window.
acperkins:
it sounds like being in design mode is what is causing a problem with the code you suggested. How do I turn design mode off or elect not to use design mode. I took a quick look though Options and query designer but did not see anything about design mode. Typically I open a table, choose the SQL button at the top and edit the Select statement that is the default.
How should I be testing these queries?
thanks,
pat
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks everyone for staying with me on this. I made several mistakes in this posting, accepting an solution too soon, posting bad code that was missing a parenthisis and now I can't find Lowfatspread's post that I used for my solution.
Lowfatspred. Thank you for your EXISTS statement. That worked and I am using it.
acperkins, Thank you for clearing up so much and showing me something new in SSMS.