Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Tampa, Florida
    Posts
    13

    Question Unanswered: Simple SQL statement question

    Is there any way i can rewrite this query in one select statement, rather than 2 selects.

    update leads_tb
    set city=
    (select city
    from market_zips_rel_tb
    where zip ='33617'
    ),
    state_id=
    (select state_id
    from market_zips_rel_tb
    where zip ='33617'
    )
    where leads_tb.lead_id = 72;

    Thanks,
    appreciate your help

  2. #2
    Join Date
    May 2003
    Location
    Tampa, Florida
    Posts
    13

    Thumbs up I found the solution

    I found the solution by writing a trigger

    DECLARE
    -- Declare a variable to hold the state_id
    st_id INTEGER ;

    -- Declare a variable to hold the city.
    ct varchar;

    begin

    SELECT INTO st_id,ct state_id,city FROM market_zips_rel_tb WHERE zip =New.zip;
    IF NOT FOUND THEN
    st_id =0;
    ct='N/A';
    END IF;

    update leads_tb
    set city=ct , state_id=st_id
    where leads_tb.lead_id = New.lead_id;

    return null;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •