CODEWITHSUNDEEP
sqlsql-server
blay
blay

Reputation: 225

Updating parent table

i have two tables namely profile and table_1, profile is a parent table and table_1 is a child table. i have a email column in both tables and what i want to do is for example '[email protected]' in the child table column email should update every column of the parent table column record with 1 where parent email column is [email protected].

in the where statement i use both primary keys of both tables to do it but since they are not the same it causes mistakes. 

update profile 
  set record= (
               select table_1.record
                 from Table_1
                where profile.profile_id = Table_1.profile_id
              )

but when i used this one below which i know will work it give me an error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >="

update profile 
  set record = (
               select table_1.record
                 from Table_1
                where profile.email = Table_1.email
              )

please how do i make the update work with the email columns but not the primary key columns

Upvotes: 0

Views: 57

Answers (3)

Raj More
Raj More

Reputation: 48024

I think this is what you may want:

update P
set P.record= T.record
from profile P
Inner Join Table_1 T
    ON P.profile_id = T.profile_id

Any row in P that matches the ProfileId in T will get it's "Record" column updated with the value in T's "Record" column.

You can modify this to use other columns in the JOIN. You can also add multiple columns to update in the SET portion of the code.

Upvotes: 0

hsd
hsd

Reputation: 56

Since this is a parent-child table you must have some joining condition. I think you need to add a where condition at the end of the query mentioned in the above answer:

        update P
        set P.record= T.record
        from profile p 
        Inner Join Table_1 T
        ON (Since parent-child, you must have a joining condition)
        where p.email = t.email

Upvotes: 0

T I
T I

Reputation: 9943

If record is functionally dependant on email then derive a set of email, record and use that for your update

with s as (select distinct email, record from table1)
update t
set record = s.record
from profile t
join s on s.email = t.email

If for s, email /-&gt; record then the record you get from the join is not guaranteed

Upvotes: 3

Related Questions