Sqlite 3.4 updating table with data from different table – changed with 3.3 – newbie question

join;sqliteupdate

All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in table GULLfromLG (currently empty), with data from the issbn field in BIBinfo (field can be empty in a given record).

I am using SQLiteStudio. From looking through the documentation, sqlitetutorial, w3resource, and general web searches I believe the following should work. But it doesn't. The processing happens, is declared successful, but all the fields in GULLfromLG are empty. What am I missing?

    UPDATE GULLfromLG
   SET issbn = (
   select issbn
  FROM BIBinfo
 WHERE bibno = bibno)

I have also tried naming the fields with variations of
GULLfromLG.issbn GULLfromLG.bibno BIBinfo.issbn BIBinfo.bibno

Any help would be greatly appreciated!

Best Answer

You need to gove sqllite the chance to know which column from which table you mean

UPDATE GULLfromLG
   SET issbn = (
   SELECT issbn
  FROM BIBinfo
 WHERE bibno = GULLfromLG.bibno)
[Example](http://sqlfiddle.com/#!5/c6687/1)

Related Solutions

Sql-server – Way to update many (>3000) records with unique data, using a character limited web form

The SET can be made a bit simpler:

SET info4 = 'BNID: ' +
  CASE guestid
    WHEN 12345 THEN '917546553'
    WHEN 67890 THEN 'B04695465'
      [etc, etc.]
                          --- no ELSE here
  END 

WHERE info4 IS NULL       --- and this is needed

Saves 6 characters per case.

If you can create a table (even a temporary one) you could also use:

--- run once
CREATE TABLE #tmp
( guestid INT NOT NULL
, i4 VARCHAR(200)
, PRIMARY KEY (guestid)
) ;

--- as many times as needed
INSERT INTO #tmp VALUES
(12345,'917546553'),
...
(67890,'B04695465');

and then:

UPDATE guest
  SET info4 = 'BNID: ' + i4
FROM guest
  JOIN #tmp
    ON #tmp.guestid = guest.guestid
WHERE guest.info4 IS NULL

SQLITE table joining with group

The subquery computes, for each user in USERS, the latest msg_id value:

SELECT *
FROM MessageTable
WHERE msg_id IN (SELECT (SELECT msg_id
                         FROM MessageTable
                         WHERE msg_to   = Users.user_id
                            OR msg_from = Users.user_id
                         ORDER BY msg_id DESC    -- or by a timestamp
                         LIMIT 1)
                 FROM Users)

Best Answer

Related Solutions

Sql-server – Way to update many (>3000) records with unique data, using a character limited web form

SQLITE table joining with group

Related Question