0

I have a 2 tables. 

Table A with the following columns
Id  title  desc  type  


Table B with the following columns
Id   type

I want to basically update all the rows of Table A in which I would replace the type with the Id value of Table B that has the corresponding same type.

For example 

Table A can have
1 ,test1 ,ddd, play
2 ,test2 ,ddd2, go
3 ,test3 ,ddd3, play


Table B has
1, play
2, go

So I want to be able to run the query so Table A looks like

1 ,test1 ,ddd, 1
2 ,test2 ,ddd2, 2
3 ,test3 ,ddd3, 1

How can this be achieved in "SqlLite" query?

Snake
  • 14,228
  • 27
  • 117
  • 250
  • possible duplicate of [How do I make an UPDATE while joining tables on SQLite?](http://stackoverflow.com/questions/773441/how-do-i-make-an-update-while-joining-tables-on-sqlite) – Barmar Sep 02 '13 at 19:50

1 Answers1

1
UPDATE tableA tA
JOIN tableB tB
ON tA.type = tB.type
SET tA.type = tB.Id
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I keep getting "error near tA", Could it be that SQLITE is diff from MYSQL? I am using SQL lite – Snake Sep 02 '13 at 19:36
  • I did a search and it seems SQLite does not support Join in update. I hope I am wrong – Snake Sep 02 '13 at 19:46
  • Apparently SQLite allows correlated subqueries. See http://stackoverflow.com/questions/773441/how-do-i-make-an-update-while-joining-tables-on-sqlite – Barmar Sep 02 '13 at 19:49
  • Your question is tagged [tag:mysql], so i gave that answer. I don't know sqlite. – Barmar Sep 02 '13 at 19:51