Update Query Joining two tables in SQLite

Can't seem to get SQL command right

Page 1 of 1

0 Replies - 2703 Views - Last Post: 16 July 2009 - 08:29 AM Rate Topic: -----

#1 piranhapeanut  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 04-May 09

Update Query Joining two tables in SQLite

Post icon  Posted 16 July 2009 - 08:29 AM

I have two tables one table is called Horses and contains the following two columns (along with other info that's not relevant) HorseInRaceID which is the primary key and HorseID. My second table is called RaceData and has two columns (among others), the two columns are HorseInRaceID which is the primary key and a column called HorseID which is currently blank. I'd like to take the information from the Horses table and use it to fill in the HorseID column in RaceData. I understand that I'm violating one of the rules of relational databases about having duplicate information in two different tables, but most of my queries will be in the RaceData table and having the HorseID info listed there will simply things for me in the future (at least in my beginner mind it will simplify things). When I used Access or SQL Server
I used to do something similar to this:

SET RaceData.HorseID = Horses.HorseID
WHERE RaceData.HorseInRaceID = Horses.HorseInRaceID
INNER JOIN RaceData, Horses
ON RaceData.HorseInRaceID = Horses.HorseInRaceID

I'm not big on using those alias (think I spelled that right?) things but my SQLite book uses them all over the place. But if I need to use them to get out of the error codes I get because the UPDATE command doesn't seem to like the period in my attempt at an update. Here is what I tried...

UPDATE racedata SET racedata.Horseid WHERE racedata.horseinraceid = horses.horseinraceid
INNER JOIN racedata, horses ON racedata.horseinraceid = horses.horseinraceid

This other guy was attempting similiar thing to mine and here is his attempts: The last one is the answer that Richard Hipp (SQLite guru) gave him and it worked for him, but I can't seem to translate it to work for me in my situation and my situation seems like it should be even easier.

> UPDATE Stocks, Stocks -- SQL error: near ",": syntax error
> INNER JOIN Options
> ON Stocks.sStockSymbol = Options.sStockSymbol
> SET Stocks.bOption = 1;
>
> UPDATE Stocks
> SET bOption = 1
> INNER JOIN Options -- SQL error: near "INNER": syntax error
> ON Stocks.sStockSymbol = Options.sStockSymbol;
>
> UPDATE Stocks
> SET bOption = 1
> FROM Stocks, Options -- SQL error: near "FROM": syntax error
> WHERE Stocks.sStockSymbol = Options.sStockSymbol;
>
> UPDATE Stocks
> SET bOption = 1 -- SQL error: near "FROM": syntax error
> FROM Stocks INNER JOIN Options
> ON Stocks.sStockSymbol = Options.sStockSymbol;
>


UPDATE stocks SET bOption=1 WHERE rowid IN
(SELECT stocks.rowid FROM Stocks, Options
WHERE Stocks.sStockSymbol = Options.sStockSymbol);

I apologize if this request is lengthy, but it is my first post to any forum and I wanted to be sure I put all the information in there. Can anyone help me??

Is This A Good Question/Topic? 0
  • +

Page 1 of 1