SQL Syntax error on table update

Question

Why is that I'm getting a syntax error on the following SQL statement under sqlite?

Error reads:

SQL Error: near "SET": syntax error

UPDATE nova
       SET Nome = (select Nome from assessores where nova.ID = assessores.ID),
       SET Morada = (select Morada from assessores where nova.ID = assessores.ID),
       SET Email = (select Email from assessores where nova.ID = assessores.ID),
       SET TelfCasa = (select TelfCasa from assessores where nova.ID = assessores.ID),
       SET TelfEmprego = (select TelfEmprego from assessores where nova.ID = assessores.ID),
       SET Telemovel = (select Telemovel from assessores where nova.ID = assessores.ID),
       SET Fax = (select Fax from assessores where nova.ID = assessores.ID)
WHERE EXISTS (select * from assessores where nova.ID = assessores.ID);

If I try to fully qualify the SET field names, the error becomes:

SQL Error: near ".": syntax error

John Kugelman · Accepted Answer · 2009-10-24 18:59:06Z

7

You only need one SET at the beginning. You can also simplify the query by joining the two tables together and eliminating the sub-queries.

UPDATE nova JOIN assessores ON nova.ID = assessores.ID
SET nova.Nome        = assessores.Nome,
    nova.Morada      = assessores.Morada,
    nova.Email       = assessores.Email,
    nova.TelfCasa    = assessores.TelfCasa,
    nova.TelfEmprego = assessores.TelfEmprego,
    nova.Telemovel   = assessores.Telemovel,
    nova.Fax         = assessores.Fax;

edited Oct 24, 2009 at 18:59

answered Oct 24, 2009 at 18:51

John Kugelman

365k70 gold badges555 silver badges600 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bill Karwin Over a year ago

You're right about the usage of SET but I don't think SQLite supports multi-table UPDATE syntax. You're showing MySQL syntax.

Alexandre Bell Over a year ago

It does support it. That was not the problem. It's been a while since I last used SQL and while looking at the syntax on SQLite documentation (sqlite.org/lang_update.html) was completely missing the fact SET was not part of the field=value cycle.

Collectives™ on Stack Overflow

SQL Syntax error on table update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related