#1  
Old 7th April 2018, 08:32 AM
John Treder
 
Posts: n/a
Default How to update with different values in each row of a single column?

I want to update a single column in a table. The values are numeric, available as an array, and effectively random and different for each row.

I've tried something like

UPDATE GridColumns SET
Colsize = 17 WHERE ColIndex = 0,
Colsize = 22.5 WHERE ColIndex = 1,
Colsize = 12 WHERE ColIndex = 2

etc, but of course that doesn't work.

There could be a few hundred rows.

I might need to use parameters, but I haven't figured out how to do it.

Or is the problem so easy and obvious that I can't see it?
Delphi 10 Seattle, NexusDB 4.0015 (gotta bump that)
--
John
  #2  
Old 7th April 2018, 08:53 AM
John Treder
 
Posts: n/a
Default Re: How to update with different values in each row of a single column?

With a little more research, it looks like params won't do the job.
I know I can create an arrray of simple SQL statements in code and box them into a transaction. I guess I'm hoping for a more elegant solution.

--
Tredmill
  #3  
Old 7th April 2018, 10:02 AM
John Treder
 
Posts: n/a
Default Re: How to update with different values in each row of a single column?

John Treder wrote:

> With a little more research, it looks like params won't do the job.
> I know I can create an arrray of simple SQL statements in code and box them into a transaction. I guess I'm hoping for a more elegant solution.


It works OK with an array of statements, one for each row to be uppdated, wrapped in a transaction. I haven't tried a really big test table yet, though.

--
John
  #4  
Old 7th April 2018, 01:29 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: How to update with different values in each row of a single column?

John Treder wrote:

> John Treder wrote:
>
> > With a little more research, it looks like params won't do the job.
> > I know I can create an arrray of simple SQL statements in code and box them
> > into a transaction. I guess I'm hoping for a more elegant solution.

>
> It works OK with an array of statements, one for each row to be uppdated,
> wrapped in a transaction. I haven't tried a really big test table yet,
> though.


You can either do that, or use a case expression for the value you assign to
the Colsize column.
  #5  
Old 7th April 2018, 03:24 PM
John Treder
 
Posts: n/a
Default Re: How to update with different values in each row of a single column?

Thorsten Engler [NDA] wrote:

> John Treder wrote:
>
> > John Treder wrote:
> >
> > > With a little more research, it looks like params won't do the job.
> > > I know I can create an arrray of simple SQL statements in code and box them
> > > into a transaction. I guess I'm hoping for a more elegant solution.

> >
> > It works OK with an array of statements, one for each row to be uppdated,
> > wrapped in a transaction. I haven't tried a really big test table yet,
> > though.

>
> You can either do that, or use a case expression for the value you assign to
> the Colsize column.


Hmm.
Thinking about what likely goes on behind the Green Baize Curtain, i suspect that both approaches would work at about the same speed. Of course, only testing will give a useful answer.
If they are about equally fast, I guess it's what's easier for me to read and understand.

--
Tred
  #6  
Old 7th April 2018, 03:30 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: How to update with different values in each row of a single column?

John Treder wrote:

> Hmm.
> Thinking about what likely goes on behind the Green Baize Curtain, i suspect
> that both approaches would work at about the same speed. Of course, only
> testing will give a useful answer. If they are about equally fast, I guess
> it's what's easier for me to read and understand.


Individual statements will use an index and range (if available) and assign a
static value to all rows within that range.

CASE expression will go through all records once and assign them various
values, but has to execute the expression for every one to determine the value
to assign.
  #7  
Old 8th April 2018, 05:30 AM
John Treder
 
Posts: n/a
Default Re: How to update with different values in each row of a single column?

Thorsten Engler [NDA] wrote:

> John Treder wrote:
>
> > Hmm.
> > Thinking about what likely goes on behind the Green Baize Curtain, i suspect
> > that both approaches would work at about the same speed. Of course, only
> > testing will give a useful answer. If they are about equally fast, I guess
> > it's what's easier for me to read and understand.

>
> Individual statements will use an index and range (if available) and assign a
> static value to all rows within that range.
>
> CASE expression will go through all records once and assign them various
> values, but has to execute the expression for every one to determine the value
> to assign.


I see (dimly).

--
nhoJ


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Trigger Log for altered values with the column name John Jefkins nexusdb.public.support.sql 9 8th September 2010 03:30 AM
Re: version 2.0602: Error in IN clause: Single-column query expected David Guest nexusdb.public.support.sql 0 14th March 2007 04:01 AM
version 2.0602: Error in IN clause: Single-column query expected Dennis Landi nexusdb.public.support.sql 9 14th March 2007 03:23 AM
Re: version 2.0602: Error in IN clause: Single-column query expected Carlo Wolter nexusdb.public.support.sql 0 14th March 2007 02:51 AM
Single User SQL Update Issue Jesse Binaries 0 21st May 2006 02:41 AM


All times are GMT +11. The time now is 06:49 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.