Update all table's columns with another table's columns

Question

I want to update a table's all columns values from another table, but it doesn't work.

I get this syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM silinecek s WHERE frameworkcode IN ('AR','CF','FA','IR','KT','SER','SF','SR' at line 3

This is my query

UPDATE marc_subfield_structure 
SET liblibrarian = s.liblibrarian, libopac = s.libopac,
    repeatable = s.repeatable, mandatory = s.mandatory,
    kohafield = s.kohafield, tab = s.tab,
    authorised_value = s.authorised_value, authtypecode = s.authtypecode,
    value_builder = s.value_builder, isurl = s.isurl, hidden = s.hidden,
    seealso = s.seealso, link = s.link, defaultvalue = s.defaultvalue,
    maxlength = s.maxlength 
FROM 
    silinecek s 
WHERE 
    frameworkcode IN ('AR','CF','FA','IR','KT','SER','SF','SR','VR')
    AND tagfield='952';

What should I do?

Answer 1

The MySQL syntax for multi-table updates does not contain a FROM clause. Therefore MySQL raises an error message and tells you that the the error is near the FROM clause and that you should check the manual.

Your statement should be something like:

UPDATE 
  marc_subfield_structure m 
JOIN
  silinecek s ON m.something=s.something
SET 
    m.liblibrarian=s.liblibrarian, 
    m.libopac=s.libopac,
    m.repeatable=s.repeatable, 
    m.mandatory=s.mandatory,
    m.kohafield=s.kohafield, 
    m.tab=s.tab,
    m.authorised_value=s.authorised_value, 
    m.authtypecode=s.authtypecode,
    m.value_builder=s.value_builder, 
    m.isurl=s.isurl, 
    m.hidden=s.hidden,
    m.seealso=s.seealso, 
    m.link=s.link,
    m.defaultvalue=s.defaultvalue,
    m.maxlength=s.maxlength 
WHERE s.frameworkcode IN ('AR','CF','FA','IR','KT','SER','SF','SR','VR')
AND s.tagfield='952';

But I don't know either the structure of your tables or the purpose of your query, so that is just to give you the idea about the correct syntax.

Answer 2

You (can) get multiple rows from silinecek. This is not correct you must make sure that you get one and only one row. This you get by adding a join between the 2 tables.