• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Delphi and sql

I like to make some copies of some sql records.
I like to do this whitout storing the information in a temporary variable.

say that I have a table test with the records
a b c d e f.

I like to do a select * from test where a=1
and then insert a new (or more than one if the select return more than one answer)
record with a=2 and b,c,d,e and f should be the same as in the result from the select above.

Can someone help me out with this....
0
pucko
Asked:
pucko
  • 9
  • 7
  • 3
  • +1
1 Solution
 
ITugayCommented:
Hi pucko,
here is it:

insert into test (a, b, c, d, e)
select 2,b,c,d,e from test where a = 1

------
Igor

PS: be careful about new value for "a". It should not exists in the table.
0
 
kretzschmarCommented:
maybe you should expalin a bit more

insert into atable select 2, b, c, d, e, f from atable where a = 1
0
 
kretzschmarCommented:
hi igor ;-)
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
ITugayCommented:
hi :-)
0
 
puckoAuthor Commented:
I think I explaind a bit stupid.

I have a table with the field_names a, b, c , d

a                b            c              d
------------------------------------------------?
1               2             3              4
2               3             5              6
3               4             7              9

say that I like to do a select * from table where a=2

it gives me 2 3 5 6

Now I like to have this row added whit a new a value (select max a)+1

so the table looks like

1          2      3     4
2          3      5     6
3          4      7     9
4          3      5     6


Is this what you described abowe?
0
 
KristianCommented:
you ar a bit vague here do you mean you want to add extra rows manually or columns?
0
 
ITugayCommented:
Hi pucko,
yes, seems my sample does exactly what you mean

if you want to generate new value for a = max(a)+1 then it would be:

insert into test (a, b, c, d)
select (select max(A)+1 from test),b,c,d from test where a = 1

-----
Igor.
0
 
puckoAuthor Commented:
I like to insert a extra row that are a copy of another row (but with a new a value.)

is it easyer if I say that I have a table
that looks like this:

id       car        color
---------------------------
1        volvo       blue
2        fiat        green
2        ferarri     yellow
2        saab        red
3        opel        green

and I like to loog for every car with id=2
and create a new record with it.

here I like to insert:

4       fiat       green
4       ferarri    yellow
4       saab       red

because I make a select * from table where id=2

SO after I like to have this table:

id       car        color
---------------------------
1        volvo       blue
2        fiat        green
2        ferarri     yellow
2        saab        red
3        opel        green
4        fiat        green
4        ferarri     yellow
4        saab        red


0
 
ITugayCommented:
Hi pucko,

yes, I understand you. Try my sample, it is excactly what you need.

-----
Igor
0
 
puckoAuthor Commented:
Mabe I missunderstand you:

I do a select * from test where a=7 b=1 c=1:


it gives me:

a   b  c    d   e
7   1  1    1   7
7   1  1    1   8

then I make a

insert into table (a,b,c,d,e) select 411,b,c,d,e from test where a=7,b=1,c=1

and try
a select * from test where a=411 and b=1 and c=1

and it gives me 0 rows....
0
 
ITugayCommented:
Hi pucko,
sounds very strange, I just checked it again, all seems fine for me.
btw, I checked it for Interbase.

-----
Igor.
0
 
puckoAuthor Commented:
Sorry that was me that miss-spelled something I think.
But I still have one problem.

my sql statement becomes more than 255 char so I get:

[Error] Nusse.pas(3610): String literals may have at most 255 elements



The row it complains over is :


 Buf:= 'insert into q_block(quote_no,version,alternate,block_no,block_id,header,blockfactor,arrange,extra1,extra2,
extra3,extra4,extra5,
extra6,extra7,extra8,extra9,extra10,remark,block_type) select max_no,,version,alternate,block_no,block_id,header,blockfactor,arrange,extra1,extra2,extra3,extra4,extra5,extra6,
extra7,extra8,extra9,extra10,remark,block_type from q_term where quote_no=' +IntToStr
(ThisQuotation.quote_no)+ ' and alternate=' + intToStr(ThisQuotation.alternate) + ' and version=' + intToStr
(ThisQuotation.version)


How can I by-pass this?
0
 
puckoAuthor Commented:
Fortget... I use wideString instead
0
 
puckoAuthor Commented:
Thanks for the help...

0
 
ITugayCommented:
Hi pucko,
OK, I hope my help was usefull for you :-)
Thanx for points,
Igor.
0
 
puckoAuthor Commented:
(Edited by Computer101)... thje wideString doesn't work either.... what shall I do,.
0
 
kretzschmarCommented:
which delphi version do you use?

if you can't declare

buf : string; //lower delphi versions are limited to 255 chars

then use a tstringlist instead

meikl ;-)

igor, you got it :-))
0
 
ITugayCommented:
here is the way:

var
  Q: TQuery;

begin
....
  Q.SQL.Clear;
  Q.SQL.Add('insert into q_block(quote_no,version,alternate,block_no,block_id,header,');
  Q.SQL.Add('blockfactor,arrange,extra1,extra2,extra3,extra4,extra5,extra6,extra7,extra8,');
  Q.SQL.Add('extra9,extra10,remark,block_type) select');
....
  Q.ExecSQL;

-----
Igor.
0
 
puckoAuthor Commented:
Thnks...
0
 
puckoAuthor Commented:
Still have one problem:

I have this:


      try
        Query1.Close;
        Query1.SQL.Clear;
        Buf:= 'insert into q_term (quote_no,version,alternate,cust_alt,id,idx,termtext,type,sortorder)'+
        ' select ' + intToStr(max_no)+',version,alternate,cust_alt,id,idx,termtext,type,sortorder from '+
        'q_term where quote_no=' +IntToStr(ThisQuotation.quote_no)+ ' and alternate=' + intToStr(ThisQuotation.alternate)
        + ' and version=' + intToStr(ThisQuotation.version);
        Query1.SQL.Add(Buf);
        Query1.Open;
      except on E: Exception do
        begin
          Screen.Cursor:= crDefault;
          Application.MessageBox(PChar(E.Message), PChar(Application.Title), mb_OK+mb_IconStop);
          Exit;
        end;
      end;



And I get the error message:

E.Database Error with message

Query1: CommandText does not return a result set.

When I toggle to inspect mode and cut the sql query from the inspector into the queryAnalyzer the sql query does not give any errors.

What is wrong in my code?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now