CODEWITHSUNDEEP
sqlms-access
Maldred
Maldred

Reputation: 1104

SQL UPDATE from Table 2 to Table 1 WHERE

I'm trying to update Table 1 from the data in Table 2, where the IDs match... Currently trying the below SQL script and its not working for me! Any help would be great.

UPDATE tbleVendorData
   SET tbleVendorData.VendorActive = tbleTemporary.F7
  FROM tbleTemporary
 WHERE tbleTemporary.F1 (SELECT VendorNumber FROM tbleVendorData)

Nothing is happening at all to Column "VendorActive" when I run this script, I was hoping it would update everything from tbleTemporary.F7

Upvotes: 0

Views: 56

Answers (3)

Basil Battikhi
Basil Battikhi

Reputation: 2668

since you declare which table should be edited then there is no need to write the table again before the column name, However you have to assign some condition at the last line. if do you want to update all records which can be retrieved from (SELECT VendorNumber FROM tbleVendorData) then your query should be 

UPDATE tbleVendorData
   SET tbleVendorData.VendorActive = tbleTemporary.F7
  FROM tbleTemporary
 WHERE tbleTemporary.F1 in (SELECT VendorNumber FROM tbleVendorData)

if do you want to update one record then you have to set limit at the end of query

however if did you write this query like 

UPDATE tbleVendorData
       SET tbleVendorData.VendorActive = tbleTemporary.F7
      FROM tbleTemporary
     WHERE tbleTemporary.F1 = (SELECT VendorNumber FROM tbleVendorData)

then the query will not be executed because it will retrieve to many records and it cannot be compared to 1 column but if you do really want to use '=' operator then your query should be look like this 

 UPDATE tbleVendorData
           SET tbleVendorData.VendorActive = tbleTemporary.F7
          FROM tbleTemporary
         WHERE tbleTemporary.F1 = (SELECT VendorNumber FROM tbleVendorData limit 1)

at the above case will it will update the first record which matched with vendor number

Upvotes: 0

MichaelEvanchik
MichaelEvanchik

Reputation: 1766

UPDATE vendor
SET vendor.VendorActive = temp.F7
FROM tbleVendorData vendor 
join tbleTemporary temp on vendor.VendorNumber = temp.F1 --this is my guess
--    WHERE temp.F1 in (SELECT VendorNumber FROM tbleVendorData)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Presumably, you intend something like this:

UPDATE tbleVendorData INNER JOIN
       tbleTemporary
       ON tbleTemporary.F1 = tbleVendorData.VendorNumber
   SET tbleVendorData.VendorActive = tbleTemporary.F7

Upvotes: 1

Related Questions