SQL - does it work like this?! I'm a total SQL noob...

June 10, 2021 6:29 AM

Basically I'm trying to update some records in our EPOS database because the transactions relate to last month but they've been input for this month. There's about 25 of them so I thought rather than manually doing them one by one in SQL Workbench and possibly making mistakes I'd try to learn how to do it more automatically. THANK YOU

I get the feeling this is easy for SQL, perhaps its bread and butter, but I'm totally new to it... I'm wanting to query table CUST_TRAN and find out the 'ID' for value $1 which is in column 'INVOICE'. I then want to use this newly discovered 'ID' to update table 'TRANSACTS' to change the DATE and DATE_TIME (two values in two columns) to value $2 using the column IDFROMTRAN to match the ID against...there are often TWO rows with the same IDFROMTRAN that need backdating because the customer signs for the goods and the act of signing creates its own row in the db.

Does any of this make sense?!

Basically to explain what I'm doing I'm changing the date on transactions to put them into last month so that they'll be invoiced correctly and reflect when the customer was actually in store.

TLDR: How do I find out an alternative ID for a transaction then use that to change the date in two rows in another table? Thank you!

Does it have to be done in stages or can it be done as one transaction for each query?

posted by dance to Computers & Internet (12 answers total)

I'm making a guess here, but are you trying to just update the first of the two matching rows in TRANSACT? Or both rows? (I assume the first because the second case is trivial.) What's the unique key of TRANSACT? That is, how can you differentiate between the two entries with the same IDFROMTRAN? (Also, is there a case where the purchase occurs in one month and the signing happens in the subsequent month? Or does that case even matter?)
posted by SPrintF at 6:47 AM on June 10, 2021

Do you mean something like:

UPDATE TRANSACTS TR set DATE=$2 where IDFROMTRAN in (SELECT ID from CUST_TRAN where INVOICE=$1);
posted by vacapinta at 6:51 AM on June 10, 2021

Thinking about it a little more: are you backdating the TRANSACT entries to the date of the INVOICE, rather than the date of the payment or assignment of goods? That seems odd to me. The date of the INVOICE seems entirely distinct from either of those dates. (I mean, it's your database, but I'm wondering if I'm failing to understand the problem you're trying to solve.)
posted by SPrintF at 6:53 AM on June 10, 2021

I'm not sure I follow your specific example, but here's the general way I'd think about this:

First, identify the rows that you want to update. Write a SELECT query that returns them:


SELECT *
FROM transacts
JOIN cust_tran ON transacts.idfromtran = cust_tran.invoice
WHERE ???


I'm not sure what your WHERE should be. How do you single out these 25 transactions? Are they the only transactions in this month, in which case you could say something like

WHERE date > '2021-06-01'

Or is there some other way you identify them?

Once you have that SELECT written, you can drop it into an UPDATE as vacapinta suggests.


UPDATE transacts
SET date=date - INTERVAL('1 month'), date_time = date_time - INTERVAL('1 month')
WHERE idfromtran IN (
SELECT idfromtran
FROM transacts
JOIN cust_tran ON transacts.idfromtran = cust_tran.invoice
WHERE ???
);


I don't know what database you're dealing with. DBs are inconsisten about date math. The INTERVAL syntax here is just an example.
posted by aneel at 6:55 AM on June 10, 2021 [1 favorite]

The way you would do this in a professional environment since there are only 25 and to ensure data integrity is basically do your select statement on your first table to get the IDs of the records you are trying to update.

select invoice .. from cus_tran where ...

Then your second query is an update, each one individually. You can copy them in EXCEL or something and make a script to update them sequentially all at the same time.

update TRANSACTS set date = 'date' where IDFROMTRAN = ID;

Be sure to type 'commit' after your updates if your database doesn't autocommit.
IF you mess up, type 'rollback' and it will undo your updates.

Especially if you are new to SQL, you should not be writing nested queries.
posted by The_Vegetables at 7:34 AM on June 10, 2021 [6 favorites]

2nding The_Vegetables: a production system is not the place to be trying out SQL statements that you're unsure how they'd work. Get the list of record IDs, then update each one using its record ID. Do your learning on a non-production database.
posted by Aleyn at 7:59 AM on June 10, 2021 [6 favorites]

Now that I understand that you're working with a point-of-sale database, your question makes more business sense to me now. Sorry.

My approach to trying something new in a production system is: ALWAYS LEAVE YOURSELF A WAY OUT. I like the above suggestions of not trying anything clever like nested queries. My approach to situations like this is: extract the data into a file (tab-separated, that you can pull into Excel) including both BEFORE and AFTER values. LOOK at what your process will do. Then, if it looks OK, import the data into a table somewhere and use a FOR loop to perform the transactions one-at-a-time. I strongly suggest ordering the data in some fashion so that if the update fails part way through, you'll know what prior transactions COMMITted successfully, which one failed, and which remain to be processed. Have an audit trail.

And, yes certainly, try this all out on a test database if at all possible.
posted by SPrintF at 8:53 AM on June 10, 2021

Yes, it's easy to run an UPDATE and accidentally update every row in the database, or something equally awful. When I issue SQL against a production database, I prepare for it like a surgeon might prepare for an operation. I also ask "is this surgery absolutely necessary?" I'm wondering if your EPOS vendor has a way to backdate invoices that doesn't involve direct SQL statements, or at least can confirm the precise SQL statements required.
posted by RobotVoodooPower at 10:57 AM on June 10, 2021

Response by poster: Just wanted to say thanks to everyone for all the help.

There is a non-live db I will be testing against.

The 25 transactions that need singling out - I have the invoice numbers for them, a list of them, I just need to cross-reference the invoice numbers with the actual db IDFROMTRAN to process them further.

I guess these two answers:

UPDATE TRANSACTS TR set DATE=$2 where IDFROMTRAN in (SELECT ID from CUST_TRAN where INVOICE=$1); (acapinta)

UPDATE transacts SET date=date - INTERVAL('1 month'), date_time = date_time - INTERVAL('1 month') WHERE idfromtran IN (SELECT idfromtran FROM transacts JOIN cust_tran ON transacts.idfromtran = cust_tran.invoice WHERE ??? ); (aneel)

were exactly what I was wondering about. They're quite different, though, so I naively ask - is one pseudocode? Or is one more verbose to help a n00b? Or is more more succinct because it's more efficient code?

Just trying to learn.

And thanks again!
posted by dance at 2:32 PM on June 10, 2021

The first code is assuming you want to literally update the date valye to $2 and that only the instances you want to update will have a value of $1. I assume these are are standing in for some other values. If there are other records in the table that have a value of $1 that you don’t want to update, that won’t work.

The second code snippet is decrementing the date and date/time fields by exactly a month - so that if the original date was 5/10/2021 the new date would be 4/10/2021. The subquery assumes you have to look up values in your invoice table to find the values you want to change in the transactions table (that's what the join does).
posted by jeoc at 3:44 PM on June 10, 2021 [1 favorite]

Best answer: Okay, I think I was confused about ID/INVOICE. Let's say the invoice IDs of the invoices you want to change are 101, 102, 103.

So to find the right CUST_TRAN rows you'd do this:

SELECT *
FROM cust_tran
WHERE cust_tran.invoice IN (101, 102, 103)


I'm assuming that CUST_TRAN has an ID column. So we want to match up the rows from TRANSACTS with those IDs. The SQL for "match up" is JOIN. This shows us all of the columns in both the CUST_TRAN and TRANSACT tables for the rows that match up, so you can compare them and make sure the matching worked the way you wanted:

SELECT *
FROM transacts
JOIN cust_tran ON transacts.idfromtran = cust_tran.id
WHERE cust_tran.invoice IN (101, 102, 103)


Another way to "match up" that might make more intuitive sense is with a subquery:

SELECT *
FROM transacts
WHERE idfromtran IN (SELECT id FROM cust_tran WHERE invoice IN (101, 102, 103))


Okay, now that we're sure we're looking at the right rows, we can consider changing them. What do we want to change them to? Well, if we just want to change them all to a single date, we could use:

SET date = '2021-05-01'


Or if we want to change them by a fixed amount, we can use date math:

SET date = date - INTERVAL('1 month')


Once we know how we want to change the rows, we use UPDATE to do it:

UPDATE transact
SET date = date - INTERVAL('1 month')
WHERE idfromtran IN (SELECT id FROM cust_tran WHERE invoice IN (101, 102, 103))



If we have a list of dates, each different, matched up with the list of invoice IDs, there probably isn't a general SQL way to do this neatly. Your database probably has some way of loading a CSV or something as a temp table and doing it. That's going to be pretty database-specific.
posted by aneel at 6:25 PM on June 10, 2021 [1 favorite]

Response by poster: Thank you this is amazing, I am so grateful. So much 'kinder' and more conversational than Stackoverflow would have been. Cheers!!
posted by dance at 2:19 AM on June 11, 2021 [1 favorite]