Update the default_terms_id of the vendors table and make it equal to the terms_

Question

Update the default_terms_id of the vendors table and make it equal to the terms_id of the invoices table, for those vendors who have positive credit total (credit_total>0), yet there were no payments (payment_total=0) on the system.
a.       You will hardcode the default_terms_id in the update statement. But you need to write first a select query to show only the records that will be changed.
b.       Update records (two update commands)
c.       Use a select statement to show the updated records (vendor_name, default_terms_id, terms_id, invoice_number, invoice_total, credit_total, payment_total)

Submit the following:
i.   The select query of question (a.) and its result.
ii.   The two update commands.
iii.   The result of the select query in (c.) to show that the update was correctly done

Explanation / Answer

i) SELECT default_terms_id, terms_id FROM vendors v, invoices i WHERE terms_id<>default_terms_id && credit_total>0 && payment_total = 0;

ii)

UPDATE vendors SET default_terms_id = '10' WHERE default_terms_id = (SELECT default_term_id FROM vendors v, invoices i WHERE terms_id<>default_terms_id && credit_total>0 && payment_total = 0);

UPDATE invoices SET terms_id = '10' WHERE terms_id = (SELECT terms_id FROM vendors v, invoices i WHERE terms_id<>default_terms_id && credit_total>0 && payment_total = 0);

iii) SELECT * FROM terms, invoices WHERE terms_id = default_terms_id && terms_id=10;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.