16x Prompt for SQL

You can use 16x Prompt to generate SQL scripts for querying, updating, and managing databases.

Here we use a sample database with two tables, bill and receipt, to demonstrate the use cases.

Update

Update status column in Bill table based on Receipt table

We want to update the status column in the bill table based on the information in the receipt table.

Step 1: Enter Task Instruction in the 16x Prompt app.

Write MySQL 5.7 code to update status column in `bill` table with information from `receipt` table.
 
If the bill is fully paid, then the status should be updated to 'PAID'.
 
If the bill is partially paid, then the status should be updated to 'PARTIAL'.
 
If the bill is not paid at all, then the status should be updated to 'UNPAID'.

Step 2: Drag and drop SQL files into the 16x Prompt app.

• bill.sql
• receipt.sql

You should see the Final Prompt in 16x Prompt:

Step 3: Generate SQL code using ChatGPT.

You can either copy paste the final prompt into the ChatGPT, or send it via API to get the SQL code.

Here is the ChatGPT (ChatGPT Classic) Output:

UPDATE bill b
LEFT JOIN (
    SELECT bill_id, SUM(amount) AS total_paid
    FROM receipt
    GROUP BY bill_id
) r ON b.id = r.bill_id
SET b.status = CASE
    WHEN r.total_paid IS NULL THEN 'UNPAID'
    WHEN r.total_paid < b.amount THEN 'PARTIAL'
    WHEN r.total_paid >= b.amount THEN 'PAID'
END;

After running this SQL script, the status column in the bill table will be updated based on the information in the receipt table:

Reference Links:

• ChatGPT Share Link: Update Bill Status
• Source Code: sql