Can I use subqueries to join two tables and update a subset of multiple rows in table 1?

• Jenni-268027

  Newbie

  Points: 2

  More actions

  May 1, 2008 at 2:38 pm

  #72027

  I need to update one column in a subset of rows from Table 1 with the concatenated value of the column value to be updated in Table 1 and a value from the related record in Table 2. I need to do this for multiple records.

  Table 1:

  certificate

  -----------------------------------------------

  | invoice_num | policy_num | status|

  -----------------------------------------------

  | 1| 12345| CGP|

  -----------------------------------------------

  | 2| 12346| EGP|

  -----------------------------------------------

  | 3| 12347| CGP|

  -----------------------------------------------

  | 4| 12348| CP|

  -----------------------------------------------

  | 5| 12349| EP|

  -----------------------------------------------

  | 6| 12350| EGP|

  -----------------------------------------------

  | 7| 12351| CP|

  -----------------------------------------------

  Table 2:

  ins_details

  -------------------------------

  | invoice_num | policy_form|

  -------------------------------

  | 1| AA|

  -------------------------------

  | 2| BB|

  -------------------------------

  | 3| AA|

  -------------------------------

  | 4| AA|

  -------------------------------

  | 5| BB|

  -------------------------------

  | 6| BB|

  -------------------------------

  | 7| BB|

  -------------------------------

  NOTE:

  certificate.invoice_num and certificate.policy_num are both unique

  cerificate.invoice_num is id,

  ins_details.invoice_num is also unique and joins on certificate.invoice_num - 1:1 relationship

  I need to achieve the following updates to certificate table:

  certificate

  -----------------------------------------------

  | invoice_num | policy_num | status |

  -----------------------------------------------

  | 1 | 12345AA | CGP |

  -----------------------------------------------

  | 2 | 12346BB | EGP |

  -----------------------------------------------

  | 3 | 12347AA | CGP |

  -----------------------------------------------

  | 4 | 12348 | CP |

  -----------------------------------------------

  | 5 | 12349 | EP |

  -----------------------------------------------

  | 6 | 12350BB | EGP |

  -----------------------------------------------

  | 7 | 12351 | CP |

  -----------------------------------------------

  The following query will work for only one record and only if the invoice_num or policy_num is known.

  UPDATE

  certificate

  SET

  policy_num =

  ( SELECT

  c.policy_num + i.policyForm AS new_policy_num

  FROM

  certificate c

  JOIN IAP_InsDetails i ON c.invoice_num = i.invoiceNum AND c.invoice_num = 1 )

  WHERE

  invoice_num = 1

  My problem is that I have 100's of records that need to be updated based on the value of status in the certificate table.

  Instead of updating 1 record, I need to update certificate.policy_num with the concatenated value of certificate.policy_num + ins_details.policy_form where certificate.status IN ('CGP','EGP').

  Is there a way that I can use one update query and subqueries to update my entire subset in certificate table?

  Thanks.

• Todd Engen

  SSCrazy Eights

  Points: 9869

  More actions

  May 1, 2008 at 3:50 pm

  #809847

  Is there a way that I can use one update query and subqueries to update my entire subset in certificate table?

  To update all the records you could place your selection criteria in a FROM clause instead of using a subquery.

  UPDATE certificate

  SET policy_num = t1.policy_num + t2. policy_form

  FROM certificate t1

  JOIN ins_details t2 ON t2.invoice_num = t1.invoice_num AND t1.status IN ('CGP', 'EGP')

  CREATE TABLE certificate

  (

  invoice_num INT,

  policy_num VARCHAR(20),

  status VARCHAR(10)

  )

  INSERT INTO certificate

  SELECT 1,'12345', 'CGP'

  UNION

  SELECT 2,'12346', 'EGP'

  UNION

  SELECT 3,'12347', 'CGP'

  UNION

  SELECT 4,'12348', 'CP'

  UNION

  SELECT 5,'12349', 'EP'

  UNION

  SELECT 6,'12350', 'EGP'

  UNION

  SELECT 7,'12351', 'CP'

  --

  CREATE TABLE ins_details

  (

  invoice_num INT,

  policy_form CHAR(2)

  )

  INSERT INTO ins_details

  SELECT 1, 'AA'

  UNION

  SELECT 2, 'BB'

  UNION

  SELECT 3, 'AA'

  UNION

  SELECT 4, 'AA'

  UNION

  SELECT 5, 'BB'

  UNION

  SELECT 6, 'BB'

  UNION

  SELECT 7, 'BB'

• Jenni-268027

  Newbie

  Points: 2

  More actions

  May 1, 2008 at 4:02 pm

  #809853

  Perfect. Many thanks!