Hi Tim,

I have a table with first name and last name, I am trying to change the last name is its there or add the record if its not I created the following code, but it does not
compile, please advice by showing me the correct code, thanks, David

BEGIN

DECLARE stmt STATEMENT;
DECLARE stmt1 STATEMENT;
DECLARE stmt2 STATEMENT;

PREPARE stmt FROM 'UPDATE people SET lastname=''Adams'' WHERE lastname=''Smith''';
PREPARE stmt1 FROM 'Insert into people values(''Mike'',''Adams'');';
PREPARE stmt2 FROM 'select * from people where firstname=''Mike'' and lastname=''Smith'';';

IF  (Exists (Execute stmt2)) THEN
    Execute stmt;
ELSE
    Execute stmt1;
END IF;

END

David,

<< I have a table with first name and last name, I am trying to change the
last name is its there or add the record if its not I created the following
code, but it does not compile, please advice by showing me the correct code,
thanks, >>

Here you go:

SCRIPT
BEGIN

DECLARE stmt STATEMENT;
DECLARE stmt1 STATEMENT;
DECLARE stmt2 STATEMENT;

PREPARE stmt FROM 'UPDATE people SET lastname=''Adams'' WHERE
lastname=''Smith''';
PREPARE stmt1 FROM 'Insert into people values(''Mike'',''Adams'');';
PREPARE stmt2 FROM 'select * from people where firstname=''Mike'' and
lastname=''Smith'';';

EXECUTE stmt2;

IF  (ROWSAFFECTED(stmt2) > 0) THEN
    Execute stmt;
ELSE
    Execute stmt1;
END IF;

END