CODEWITHSUNDEEP
sqlite
Prabha
Prabha

Reputation: 135

SQLite - Update a column based on values from two other tables' columns

I am trying to update Data1's ID to Record2's ID when:

Record1

| ID | Weight | Name |
|----|--------|------|
|  1 |     10 |    a |
|  2 |     10 |    b |
|  3 |     10 |    c |

Record2

| ID | Weight | Name |
|----|--------|------|
|  4 |     20 |    a |
|  5 |     20 |    b |
|  6 |     20 |    c |

Data1

| ID | Weight |
|----|--------|
|  4 |     40 |
|  5 |     40 |

I have tried the following SQLite query:

update data1
set id = 
  (select record2.id 
   from record2,record1 
   where record1.name=record2.name 
   and record1.weight<record2.weight)
where id in
  (select record1.id
   from record1, record2
   where record1.name=record2.name
   and record1.weight<record2.weight)

Using the above query Data1's id is updated to 4 for all records.

NOTE: Record1's ID is the foreign key for Data1.

Upvotes: 0

Views: 93

Answers (1)

Abecee
Abecee

Reputation: 2393

For the given data set the following seems to serve the cause:

update data1
set id = 
  (select record2.id 
  from record2,record1 
  where 
   data1.id = record1.id
   and record1.name=record2.name 
   and record1.weight<record2.weight)
where id in
  (select record1.id
  from record1, record2
  where
    record1.id in (select id from data1)
    and record1.name=record2.name 
    and record1.weight<record2.weight)
;

See it in action: SQL Fiddle.

Please comment if and as this requires adjustment / further detail.

Upvotes: 1

Related Questions