in reply to CONCAT Perl or SQL

Is the destination column in the same table, or a different table?

If it's the same table, you can do something like this: 

update table_name
   set destination_column = "A" + source_column
 where some_condition

[download]
You can also do the same thing if the source and destination columns are in different tables. I suggest going to Microsoft's web site and reading up on the UPDATE command in T-SQL.

Michael

Replies are listed 'Best First'.
Re: Re: CONCAT Perl or SQL
by Win (Novice) on Dec 03, 2003 at 15:25 UTC

    I have tried several variations on a theme including: 

    my $sthG = $dbh->prepare("INSERT into ICD_9_Disease_Types (ICD_9_code)
+ SELECT CONCAT('A', select ICD_9 from ICD_9 where ICD_9 > 0);") or di
+e "Couldn't prepare query: ".$dbh->errstr;

    [download]

    I am transferring between two different tables.

[reply]
[d/l]
      Please go read the Transact-SQL manual.

      I just showed you above that you concatenate strings in SQL using the '+' operator:  select "A" + ICD_9 ...

      You are at a point where you don't yet understand SQL - I strongly suggest that until you understand SQL you leave perl and DBI out of it, and write the queries that you want to execute in a text editor and run them using isql.exe or some other tool that connects directly to the database. This will give you direct feedback on what you are doing.

      Are you inserting a NEW row in the ICD_9_Disease_Types table, or are you updating an existing row?

      From the code above I am guessing that you are updating an existing row, in which case you need to know the relationship between the two tables.

      Michael

[reply]
[d/l]
      A reply falls below the community's threshold of quality. You may see it by logging in.
      my $sql = "
UPDATE ICD_9_Disease_Types
SET ICD_9_code = CONCAT('A', ICD_9_code)
WHERE ICD_9 > 0";

my $sth = $dbh->prepare($sql) or $dbh->errstr();

my $rows_updated = $sth->execute();

print "We updated $rows_updated rows\n";

      [download]
[reply]
[d/l]
        I don't think that CONCAT works with MS SQL Server. I have tried the following that does not give any error message but does not do what I want either. 
            my $sqlB = "update ICD_9_Disease_Types set ICD_9_code = ('A') + IC
+D_9 FROM ICD_9 WHERE ICD_9 > 0";

    my $sthG = $dbh->prepare($sqlB) or $dbh->errstr();
    my $rows_updated = $sthG->execute();
    
    print "We updated $rows_updated rows\n";

        [download]

        It gives the message: 

        We updated 0E0 rows

        [download]

        I am not really sure what the OEO refers to.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom