Syntax error with multiple requests

[Mark]

Dave,

You're missing the problem completely Have a close look at the syntax I posted. It is about updating multiple rows.

Mark

[dave_probertGA6e24]

Hi Mark,

How do you figure that out??

The two lines of example that Simon gives don't show them referring to separate rows. Only different fields.

Or has Simon not posted a good example? Simon??

Cheers,
Dave

[Mark]

Hi Dave,

Simon posted an example with two UPDATE commands and asked how to do this with only one command.

Kind regards,

Mark

[dave_probertGA6e24]

Mark,

And how was my answer wrong then - as it actually shows how to update the two different 'fields' in one update command - these were the differences between the lines in Simons' example. He did not show two differing row identifiers (both use gID).

If you can extract the meaning that there are two different rows from what he posted then please explain how.

Anyway - what I posted is correct for a single row multi-field update (which has been the standard for a long time now).

Let's let Simon figure out what he actually needs.

Cheers,
Dave

[Mark]

Records, Dave. Not fields.

Mark

[dave_probertGA6e24]

Mark,

Sorry, but to be precise in terminology - update multiple 'columns' (Also known as 'Fields' in some circles) in the same record (aka Row) of the table. That is what my SQL example will do.

I have been doing SQL for about 16 years and kinda know my way around the subject a bit.

The question still stands - how did you get to an update of multiple Records from the example that Simon gave. 4 lines of code - no change of the gID (the record identifier) in between them and the difference between the two SQL lines is a column identifier. How does that relate to multiple record change? Or am I missing some secret communication between you and Simon outside of the forums?

Basically what I'm getting at is that for anyone else reading this is that when answering a question the person should make sure they read the question fully and not just assume that the answer they give is the correct one (especially if it is wrong!). If someone else says they are wrong then that person should explain why the answer is wrong so that all the other viewers of the forum post can understand why it is wrong.

I, BTW, am not saying that your answer is wrong (it is perfectly correct for the process of updating multiple records), I just would like an explanation of how mine is missing the point based on the example data that was given. I do not make any assumptions regarding that - only provide an answer based on what I see.

So, I am still leaving it up to Simon to chip in with the correct meaning of what his question was about - because it is obviously causing some interpretation problems.

Cheers,
Dave

[Simon]

Hello and thank you both for your help.
I have tested Dave's method first (because it more closely resembled my code) and it does work. Thanks Dave.
Others reading this should note there is a missing quote, easy to find.

Mark, I will have to research REPLACE. In my quick reading it does sound right but I have yet to test it. Will update you both on my findings... or just stop me.

Simon

[Mark]

Simon,

As far as I can see, Dave's code doesn't do what you want. It updates only one record, not multiple.

Dave, I try to help Simon and you come here just to attack me. I don't like that.

Kind regards,

Mark

[Simon]

EDIT to my above:
I see I did misinterpret the OP request.
I am not updating multiple records only one at a time. But I guess in the end I will eventually want to post all edits at one time.

I appreciate all the answers as they have the ability to teach me more.

I will have to stretch my brain to understand what the posted differences are.

Simon

[Mark]

Simon,

You asked

It all works but can I put the changes in 1 line and just use revExecuteSQL once?

and that's perfectly in line with the original question. What do you think you misinterpreted?

Kind regards,

Mark

[dave_probertGA6e24]

Mark

No attack on you was meant, just a question regarding how you interpreted the question from Simon. You stated that I was misunderstanding the question - which I was not in fact doing and I asked for some clarification regarding your statement.

If you feel I was 'attacking' you then you should read questions a bit better and possibly look at the answers others give - because maybe they are giving a closer matching answer than yours. If, after thinking about the question again, you realise that the other answer might be better suited to the question then you should accept that. If you truly think that your answer is better suited then you should explain why - with proper reference to the original question. Nothing more.

No matter. Simon is the one who will be able to benefit from BOTH the types of SQL call in the end - along with others who read this.

Cheers,
Dave

[Simon]

Hi Mark,
I'm new to SQL and as such not sure if I understand the terminology correctly.
REPLACE, I read, has the ability to update multiple records at one time which I (now) think was what the OP was asking. My post was about just one record but with multiple changes.

I am still looking into REPLACE and it may be what I really want, just I'm being slow witted today and haven't got my head around the code. But maybe you could help?

Code: Select all

REPLACE INTO table VALUES ('id1','val1','val2',...)  VALUES ('id2','val1','val2',...) VALUES ('id3','val1','val2',...) ....

Could you show me that with my vars?
gID is a unique number in each record, then I have columns:
first_name
last_name
mobile_number
and the updated info which may or may not be the same as in the original record, I'm just replacing everything except gID:
tFirstName
tLastName
tMob
tTableName is the table.

Ahh just by writing that I may see:

Code: Select all

REPLACE INTO tTableName VALUES ('gID','first_name','last_name',...)

Nah... I'm not getting it. I really have to stop enjoying LC into the wee hours.

Simon

[Mark]

Hi Simon,

Alright, I understand what you want. Yes you misinterpreted the original question and I thought you didn't. Dave is right, sorry Dave. Simon, just use Dave's syntax, because that's the correct way to do it in this case.

Kind regards,

Mark

[Simon]

Wait wait is this it?

Code: Select all

REPLACE INTO tTableName (gID,first_name,last_name,mobile_number) VALUES ('1','tFirstName','tLastName','tMob) VALUES ('2','tFirstName','tLastName','tMob)...

Simon
EDIT:Ahh Rats, just saw your post Mark.