[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 864
  • Last Modified:

Unknown column in 'field list'

hi..

I want to when a value is updated in 'fut_event_table_edit_2' and 'fut_event_table_edit_1.id' not exists in  'fut_event_table_edit_2.id' it makes the insert.

But in fact, it is not doing it. Instead it gives me this error..

Can you help me?

Thanks,
DELIMITER $$
 
DROP TRIGGER `foot`.`add_ranking`$$
 
create trigger `foot`.`add_ranking` 
AFTER UPDATE on `foot`.`fut_event_table_edit_2` 
for each row BEGIN
	insert into fut_event_table_edit_1 (id, tname_0)
		values (fut_event_table_edit_2.id, fut_event_table_edit_2.tname_0);
END;
$$
 
DELIMITER ;

Open in new window

0
vmorais_
Asked:
vmorais_
  • 3
  • 2
2 Solutions
 
Serial326Commented:
It's because you must use "new" and "old" to reference the table that the trigger is on.

I've also made the code for testing is exist or no becaue you don't seem to test before adding and that can cause duplicate key violation.

Hope that it help you.

See the code below for example of what I'm talking about


delimiter $$
drop trigger `foot`.`add_ranking`; $$
create trigger `foot`.`add_ranking`
AFTER UPDATE on `foot`.`fut_event_table_edit_2` for each row
BEGIN
  declare nb smallint;
  select count(id) into nb from fut_event_table_edit_1 where id = new.id;
  if (nb = 0) then
    insert into fut_event_table_edit_1 values(new.id, new.tname_0);
  end if;
END; //
delimiter ;

Open in new window

0
 
Serial326Commented:
I've just made a mistake in my last post (sorry for that)

END; // should be END; $$
0
 
vmorais_Author Commented:
Want I really wanted was this..

Without your tip i would not have done it.

Do you know a way to do this better??

Thanks..
DELIMITER $$
 
DROP TRIGGER `foot`.`add_ranking`$$
 
create trigger `foot`.`add_ranking` AFTER UPDATE on `foot`.`fut_event_table_edit_2` 
for each row 
	BEGIN
  declare nb smallint;
 
	SELECT count(convocados.id) into nb
	FROM fut_event_table_edit_2 convocados
		LEFT JOIN fut_event_table_edit_1 ranking  ON (ranking.id=convocados.id)
		WHERE convocados.tname_1 = 1
		AND ranking.tname_0 is null;
	
	if (nb <> 0) then
	
	set @id = (SELECT convocados.id
			FROM fut_event_table_edit_2 convocados
			LEFT JOIN fut_event_table_edit_1 ranking  ON (ranking.id=convocados.id)
			WHERE convocados.tname_1 = 1
			AND ranking.tname_0 is null);
	
	set @nome = (SELECT convocados.tname_0
			FROM fut_event_table_edit_2 convocados
			LEFT JOIN fut_event_table_edit_1 ranking  ON (ranking.id=convocados.id)
			WHERE convocados.tname_1 = 1
			AND ranking.tname_0 is null);
 
	insert into fut_event_table_edit_1 (fut_event_table_edit_1.id, fut_event_table_edit_1.ordering, fut_event_table_edit_1.locktab, fut_event_table_edit_1.tname_0, fut_event_table_edit_1.tname_1, fut_event_table_edit_1.tname_2, fut_event_table_edit_1.tname_3, fut_event_table_edit_1.tname_4, fut_event_table_edit_1.tname_5) 
		values ( @id, '', '', @nome, '', '', '', '', '');
  
end if;
 
END;
$$
 
DELIMITER ;

Open in new window

0
 
vmorais_Author Commented:
Solution complete and accurate, my question was not accurate..
0
 
Serial326Commented:
Well I think you can do this in one shot, not sure in MySQL but I've done it lot of time in DB2.
Don't forget the "group by"


DELIMITER $$
 
DROP TRIGGER `foot`.`add_ranking`$$
 
create trigger `foot`.`add_ranking` AFTER UPDATE on `foot`.`fut_event_table_edit_2` 
for each row 
        BEGIN
  declare nb smallint;
declare @id integer;
declare @name varchar(25);
 
        SELECT count(convocados.id) into nb, convocados.id into @id, 
        convocados.tname into @name
        FROM fut_event_table_edit_2 convocados
                LEFT JOIN fut_event_table_edit_1 ranking  ON 
                (ranking.id=convocados.id)
                WHERE convocados.tname_1 = 1
                AND ranking.tname_0 is null;
        GROUP BY convocados.id, convocados.tname
        
        if (nb <> 0) then
        
 
        insert into fut_event_table_edit_1 (fut_event_table_edit_1.id, fut_event_table_edit_1.ordering, fut_event_table_edit_1.locktab, fut_event_table_edit_1.tname_0, fut_event_table_edit_1.tname_1, fut_event_table_edit_1.tname_2, fut_event_table_edit_1.tname_3, fut_event_table_edit_1.tname_4, fut_event_table_edit_1.tname_5) 
                values ( @id, '', '', @name, '', '', '', '', '');
  
end if;
 
END;
$$
 
DELIMITER ;

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now