CODEWITHSUNDEEP
postgresql
Hong Van Vit
Hong Van Vit

Reputation: 2986

column table_name of relation table_name does not exist

My project using Postgresql and Microsoft SQL Server. So I can't change the parameter name in Postgresql. How can I fix the bug in this case.

my function below.

CREATE OR REPLACE FUNCTION schedule_updatealarmsetting(
    userno integer,
    isemail boolean,
    isalarmi boolean,
    ispc boolean,
    ismobile boolean,
    timealarm integer,
    isunuse boolean)
  RETURNS void AS
$BODY$ 
BEGIN
    -- check exists
    if(exists(select 1 from ScheduleAlarmSetting A where A.UserNo = Schedule_UpdateAlarmSetting.UserNo)) then
    begin
        -- update
        update ScheduleAlarmSetting 
        set IsEmail = Schedule_UpdateAlarmSetting.IsEmail
        , ScheduleAlarmSetting.IsAlarmi = Schedule_UpdateAlarmSetting.IsAlarmi
        , ScheduleAlarmSetting.IsPC = Schedule_UpdateAlarmSetting.IsPC
        , ScheduleAlarmSetting.TimeAlarm = Schedule_UpdateAlarmSetting.TimeAlarm 
        , ScheduleAlarmSetting.IsMobile = Schedule_UpdateAlarmSetting.IsMobile
        , ScheduleAlarmSetting.IsUnuse = Schedule_UpdateAlarmSetting.IsUnuse
        , ScheduleAlarmSetting.ModDate = now()
        , ScheduleAlarmSetting.ModUserNo = Schedule_UpdateAlarmSetting.UserNo
        where ScheduleAlarmSetting.UserNo = Schedule_UpdateAlarmSetting.UserNo;
    end;
    else
    begin
        -- insert
        insert into ScheduleAlarmSetting(UserNo,IsEmail,IsAlarmi,IsPC,IsMobile, TimeAlarm, IsUnuse,RegUserNo,RegDate)
        values (Schedule_UpdateAlarmSetting.UserNo
        , Schedule_UpdateAlarmSetting.IsEmail
        , Schedule_UpdateAlarmSetting.IsAlarmi
        , Schedule_UpdateAlarmSetting.IsPC
        , Schedule_UpdateAlarmSetting.IsMobile
        , Schedule_UpdateAlarmSetting.TimeAlarm
        , Schedule_UpdateAlarmSetting.IsUnuse
        , Schedule_UpdateAlarmSetting.UserNo
        , now());
    end;
    end if;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION schedule_updatealarmsetting(integer, boolean, boolean, boolean, boolean, integer, boolean)
  OWNER TO postgres;


select * from schedule_updatealarmsetting(1,True, True, True, True,0,false);

Error column "schedulealarmsetting" of relation "schedulealarmsetting" does not exist

When I remove "schedulealarmsetting." it will show error:"column reference "userno" is ambiguous"

Upvotes: 0

Views: 96

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

In PostgreSQL, you can not use table aliases after the set command on the update command. Instead of this query:

update ScheduleAlarmSetting 
set IsEmail = Schedule_UpdateAlarmSetting.IsEmail
, ScheduleAlarmSetting.IsAlarmi = Schedule_UpdateAlarmSetting.IsAlarmi
, ScheduleAlarmSetting.IsPC = Schedule_UpdateAlarmSetting.IsPC
, ScheduleAlarmSetting.TimeAlarm = Schedule_UpdateAlarmSetting.TimeAlarm 
, ScheduleAlarmSetting.IsMobile = Schedule_UpdateAlarmSetting.IsMobile
, ScheduleAlarmSetting.IsUnuse = Schedule_UpdateAlarmSetting.IsUnuse
, ScheduleAlarmSetting.ModDate = now()
, ScheduleAlarmSetting.ModUserNo = Schedule_UpdateAlarmSetting.UserNo
where ScheduleAlarmSetting.UserNo = Schedule_UpdateAlarmSetting.UserNo;

You can use this query:

update ScheduleAlarmSetting 
set IsEmail = Schedule_UpdateAlarmSetting.IsEmail
, IsAlarmi = Schedule_UpdateAlarmSetting.IsAlarmi
, IsPC = Schedule_UpdateAlarmSetting.IsPC
, TimeAlarm = Schedule_UpdateAlarmSetting.TimeAlarm 
, IsMobile = Schedule_UpdateAlarmSetting.IsMobile
, IsUnuse = Schedule_UpdateAlarmSetting.IsUnuse
, ModDate = now()
, ModUserNo = Schedule_UpdateAlarmSetting.UserNo
where ScheduleAlarmSetting.UserNo = Schedule_UpdateAlarmSetting.UserNo;

Microsoft SQL Server and in most Databases UPDATE command writes in this format, can not using alias after SET.

Upvotes: 3

Related Questions