Update column based on most recent date time

Question

I have a statement that I want to UPDATE a row's ColumnA if ColumnB meets the LIKE query and ColumnC is the most recent date.

My ColumnC is a DateTime column and at the moment the statement ignores the request for most recent DateTime and updates all records with ColumnB.

Here it is

"UPDATE table1 SET ColumnA= '" + data +
"' WHERE ColumnB LIKE '" + variable +
"' AND MAX(ColumnC);";

What is your DBMS? Please add the appropriate tags, and also, don't use irrelevant tags (I've removed your c# tag). — rory.ap, Mar 09 '16 at 14:06
You should use SQL parameters instead of concatenating strings to prevent SQL injection, see: http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Rik, Mar 09 '16 at 14:09
Thanks for the help with tags, that's my bad! I'm aware of the sql injection, the system I'm building won't be available online. I'm building something that will be used within a company, and am not to worried about SQL injection. Should I be? — Mitchel Stuart Fountaine, Mar 09 '16 at 14:14
That's hilarious. I'm scared now, thanks! Will make changes. Testing now. — Mitchel Stuart Fountaine, Mar 09 '16 at 14:22

score 1 · Accepted Answer · answered Mar 09 '16 at 14:10

1

Your

AND MAX(ColumnC);

is not valid any DBMS as far as I know. Probably you looking to filter your ColumnC with maximum value of it with the same condition.

Pseudo code:

UPDATE table1 
SET ColumnA = data
WHERE ColumnB LIKE variable 
AND ColumnC = (SELECT MAX(ColumnC) from table1 WHERE ColumnB LIKE variable)

Also you should always use parameterized queries when you build your queries. This kind of string concatenations are open for SQL Injection attacks.

answered Mar 09 '16 at 14:10

Soner Gönül

97,193
102
206
364

Thanks Soner, that works a charm! I noticed others "Nesting" statements. I clearly understand how it works now. My only observation is your answer is missing a parenthisis. Thanks for the help! – Mitchel Stuart Fountaine Mar 09 '16 at 14:25

Update column based on most recent date time

1 Answers1