Syntax Error with Update Query When Join with Some Table

syntax error with update query when join with some table

Oracle does not support join in the update syntax:

UPDATE T123
    SET COL1 = 1,
        VER1 = VER1 + 1
    WHERE EXISTS (SELECT 1 FROM WAPTDT_123 T WHERE T123.REQUEST_ID = T.NUM_FLD);

This is standard SQL and should work in any database.

Your query has other problems as well . . . the subquery is not in parentheses, the inner join has no first table.

EDIT:

You can write this query with that subquery:

UPDATE T123
    SET COL1 = 1,
        VER1 = VER1 + 1
    WHERE T123.REQUEST_ID IN (SELECT C1 FROM ( SELECT T.NUM_FLD C1 FROM WAPTDT_123 T) TAB );

I switched this to an IN, just because that is another option. You could still use EXISTS.

SQL query syntax error, UPDATE statement with INNER JOIN

This is valid syntax:

UPDATE tblMitarbeiterUUID x

JOIN arbeiter y
ON x.idMitarbeiterUUID = y.fidMitarbeiterUUID 

SET x.dtPassword="A"  

WHERE y.id=1

SQL - update table from another table - syntax error

Error 1064 is a MySQL error. If you are using MySQL, the correct syntax is:

UPDATE matches m JOIN
       teams t
       ON m.hometeam = t.teamcode
    SET m.hometeam = t.teamname;

However, this will not really work. What you need to do is add ids:

alter table matches add hometeamcode int;

And then do:

UPDATE matches m JOIN
       teams t
       ON m.hometeam = t.teamcode
    SET m.hometeamcode = t.teamname;

EDIT:

I think I misunderstood the whole situation. Your data model is totally correct. The matches table should have the integer codes, referring to the rows in teams.

You just need to write your query to get the names:

select m.*, th.teamname as hometeamname, ta.teamname as awayteamname
from matches m join
     team th
     on m.hometeam = th.teamcode join
     team ta
     on a.hometeam = ta.teamcode;

If you don't want to do the join, then encapsulate the logic in the view.

Syntax Error using IN-clause in an UPDATE query

Use brackets instead:

UPDATE dbo.OBJECT 
SET LANGNAME = 'FooBar' 
WHERE dbo.OBJECT.[OBJEC_ID] IN (69, 42, 1337)

Or if your column name really contains quotation marks, include them as well:

UPDATE dbo.OBJECT 
SET LANGNAME = 'FooBar' 
WHERE dbo.OBJECT.["OBJEC_ID"] IN (69, 42, 1337)

SQLFiddle.

Syntax error near FROM when using UPDATE with JOIN in MySQL?

That isn't valid MySQL syntax. It is valid in MS SQL Server, however. For MySQL, use:

UPDATE 
  bestall
  JOIN beststat AS t1 ON bestall.bestid = t1.bestid 
SET view = t1.v, rawview = t1.rv

MySQL requires the update tables to come before the SET clause. See the MySQL UPDATE syntax reference for full details.

How to use an UPDATE Query with an INNER JOIN to update fields within a table

Your syntax is indeed incorrect for SQL Server - if I understand your last paragraph you just need a conditional case expression. If the following (of course untested) is not correct hopefully it's enough to put you on the right track:

update t1 set t1.Marked =
    case t2.type
      when 'Summary' then 'Yes'
      when 'Full' then 'No'
      else 'N/A'
    end
from tbl_1 t1
left join tbl_2 t2 on t1.PersNo = t2.PersNo;

Syntax error trying to update multiple tables in codeigniter

The update() method only expects a table name as first parameter, not a complete statement including a join and more. So it'll wrap the first parameter into backticks. This leads to the sql syntax error you have.

Better use query() to generate your (not straightforward) update statement.

Correct syntax for update query with inner join

I think that you want to use a WHERE clause in your UPDATE statement, which may be something like this:

UPDATE product 
SET buyPrice = :newPrice 
WHERE product_id IN (
  SELECT p.product_id 
  FROM product p JOIN user u 
  ON p.businessId = u.currentBusinessId
  WHERE sellPrice < 10
)

I use product_id in my code which I assume is the primary key of the table product.

Or, if sellPrice is a column of the table product, you could use EXISTS instead of the join like this:

UPDATE product 
SET buyPrice = :newPrice 
WHERE sellPrice < 10
  AND EXISTS (SELECT 1 FROM user u WHERE u.currentBusinessId = product.businessId)

---

Related Topics