CODEWITHSUNDEEP
mysql
user2005467
user2005467

Reputation: 13

Update Query I have two tables in a

I have two tables in a mysql database. macs and deviceinventory i want to updat macs table column name deviceid =1 but update those reords which not found in deviceinventory table column device id i use this query but it give error

UPDATE macs SET deviceid = 1 
WHERE deviceid = (SELECT deviceid FROM macs NOT IN (
                     SELECT * FROM deviceinventory.`deviceid`
                 )) ;

Upvotes: 1

Views: 57

Answers (2)

SQLGuru
SQLGuru

Reputation: 1099

try this:

UPDATE 
   macs 
SET 
   deviceid = 1 
WHERE 
   deviceid NOT IN 
     (SELECT deviceid FROM deviceinventory);

Upvotes: -1

Kevin Bowersox
Kevin Bowersox

Reputation: 94469

UPDATE macs 
SET deviceid = 1 
WHERE deviceid IN (
  SELECT deviceid 
  FROM macs 
  WHERE deviceid NOT IN (
      SELECT deviceid 
      FROM deviceinventory
  )
 ) ;

Upvotes: 2

Related Questions