Update table data using foreign key

Question

I have two tables structure like below

Table1

Serial  |   Src            | Albumid(primarykey)  
________|__________________|________
   1    |   /root/wewe.jpg |   20
   2    |   /root/wewe.jpg |   21
   3    |   /root/wewe.jpg |   21
   4    |   /root/wewe.jpg |   23
   5    |   /root/wewe.jpg |   18

Table2

Albumid |  Albumname       | AlbumCover  //albumid is secondary key ref. to first table
________|__________________|________
   20   |   AAA            |   null
   21   |   bbb            |   null
   31   |   vcc            |   null
   42   |   ddd            |   null
   18   |   eee            |   null

I followed this POST two update my Albumcover in Table2 using Serial no. of first table..

create proc AddCover @Serial int
as
Begin
update Table1 set albumcover='somthing' where table1.serial = @Serial
end

Can i do like this using foregin key constraint??

looks a like you are you are confused in fields of Table1 and Table2 , in your update statement. As Table1 does not have albumcover...!!! — H. Mahida, Aug 08 '14 at 05:45
Yea...im getting confused by seeing in the post,,Have u seen the post i posted in question...Look at the "Cascading in Action" section... — user3860465, Aug 08 '14 at 05:50
to update the album cover in 2nd table by using the Serialno. in 1St table...this is what i want to achive... — user3860465, Aug 08 '14 at 06:03
But there is no relation between table1 SerialNo and table2 columns..!! — H. Mahida, Aug 08 '14 at 06:10
Still cant understand it, from data shown here it looks like your table2 Albumid is PK and table1 Albumid is FK ref to Table2... ;( — H. Mahida, Aug 08 '14 at 06:11

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

You'll need to do the update on Table2. To tell it to have a condition based on values from table1, check this post for examples:

MySQL - UPDATE query based on SELECT Query

edited May 23 '17 at 12:20

Community

1
1

answered Aug 08 '14 at 05:45

Rachael

424
2
7

Update table data using foreign key

1 Answers1