CODEWITHSUNDEEP
sqldatabase
krosiris
krosiris

Reputation: 45

Inserting into SQL with a subquery

Could someone give me the correct syntax as to how I can insert and update data using a subquery?

SELECT PersonID 
FROM Authors
WHERE PersonID IN (select personID
               from person
               where last_name = 'Smith' AND first_name = 'Barry'
);

and update certain columns that meet this example person's criteria.

Upvotes: 0

Views: 55

Answers (3)

EGP
EGP

Reputation: 636

Regarding the Insert, I'm not 100% clear on what table you want to insert into. If you want to put 25 and Y into the Fee and Published Columns on the Authors table, then it's an update, not an insert, along the lines of what madtrubocow did, though this is how I would do it:

UPDATE a
   SET Fee=25,Published='Y'
FROM 
    Authors a
WHERE EXISTS
    (SELECT 1 FROM Person p WHERE a.PersonID=p.PersonID 
    AND p.last_name = 'Smith'  AND p.first_name = 'Barry')

If you want to insert a row into a different table that has an AuthorID,Fee and Published columns, it would be something like this:

   INSERT INTO NewTable(AuthorID,Fee,Published)
    SELECT AuthorID,25,'Y'
    FROM Authors a
    WHERE EXISTS
        (SELECT 1 FROM Person p WHERE a.PersonID=p.PersonID 
        AND p.last_name = 'Smith'  AND p.first_name = 'Barry')

I should note that I would only write these queries this way if PersonID is a unique column on the Authors table and the combo of First and Last Name is unique on the Person table. If either of those are not enforced by the DB, you'll need to give some thought as to how you ensure that you don't insert or update more rows than you intend.

Upvotes: 1

Imre L
Imre L

Reputation: 6249

insert into authors(personID, field1, field2)
select personID, 'data1', 'data2' 
from person 
where last_name = 'Smith' AND first_name = 'Barry'

Upvotes: 0

madturbocow
madturbocow

Reputation: 119

Update Authors
   set Field1='x', Field2='Y', Field3='Z'
FROM Authors
WHERE PersonID IN (select personID
               from person
               where last_name = 'Smith' AND first_name = 'Barry'
);

Upvotes: 1

Related Questions