What is the correct SQL Syntax?

Question

I am struggling to resolve a SQL syntax problem in a Laravel Eloquent query. So I've gone right back to the root, and tried to get a query working in simple SQL. And I'm stumped. I'm no expert, but this is baffling me.

I've had a look at the Update with Join in SQLite post, but I think that one is more around the issue of joins.

I'm testing this on SQLLite, which surely is representative of SQL proper?

Two tables: Instructions and Knowns. I want to update one column in the Knowns from (the latest) Instructions. Get that right, and I can solve the rest myself (I hope!).

-- This works fine
Select instructions.rowid from instructions 
where instructions.EngagementTitle not null


-- This doesn't
UPDATE knowns 
SET    EngagementTitle = instructions.EngagementTitle
WHERE  id IN (
  SELECT knowns.id 
  FROM   knowns 
  INNER JOIN instructions 
  ON knowns.reference = instructions.reference
)

Error Message

no such column: instructions.EngagementTitle: 

UPDATE knowns 
SET    EngagementTitle = instructions.EngagementTitle 
WHERE  id IN (
  SELECT knowns.id 
  FROM   knowns 
  LEFT JOIN instructions 
  ON knowns.reference = instructions.reference
)

Both tables both have the column - triple checked.

`EngagementTitle` varchar NOT NULL

What am I missing?

Outside of your sub-query, `instructions` is not defined. I think you're looking for an `UPDATE` with joins but I'm not sure if SQLite supports the syntax like MySQL does — Phil, Jun 05 '18 at 00:52
Possible duplicate of [Update with Join in SQLite](https://stackoverflow.com/questions/19270259/update-with-join-in-sqlite) — Phil, Jun 05 '18 at 00:52
I think the fosus of that one is about the specifics of a JOIN relationship — Maxcot, Jun 05 '18 at 03:04

sticky bit · Accepted Answer · 2018-06-05T01:18:53.880

UPDATE knowns 
SET    EngagementTitle = instructions.EngagementTitle 
...

The instructions table isn't "known" here, only knowns. The scope of the table expressions is "downwards" only. You can use a table introduced in a superquery in a subquery but not the other way round.

Try using subselects:

UPDATE knowns
       SET engagementtitle = (SELECT instructions.engagementtitle
                                     FROM instructions
                                     WHERE instructions.reference = knowns.reference)
       WHERE EXISTS (SELECT *
                            FROM instructions 
                            WHERE instructions.reference = knowns.reference);

I also replaced your WHERE with an EXISTS. I guess that is how you actually wanted it. It will only update rows from knowns where an entry exists in instructions for. The way you did it, with a LEFT JOIN in the subquery to IN, you'd just updated all rows, as a LEFT JOIN includes all rows from the left table, thus all IDs from knowns are in the result (You maybe wanted an INNER JOIN, that would have worked.).

But note, there must no more than one entry in instructions for one row in knowns for this to work. I silently assumed that this is the case.

Thank you, seriously.. this has been bugging me no end, and I had wondered about that whole "downwards" thing although you expressed better than I could have. And yes, the one instruction = one known is correct.Much appreciated. — Maxcot, Jun 05 '18 at 01:17

What is the correct SQL Syntax?

1 Answers1