SQL Delete and Update

Question

Hi, I start learning SQL and I have some problem.

I have one database with 4 tables.

All 4 tables have column ID.

Something like this :

Table name................Column #1..................Column #2
  Table1....................  ID......................Data1..
  Table2......................ID......................Data2..
  Table3......................ID......................Data3..
  Table4......................ID......................Data4..

I make select by ID.

SELECT Data1, Data2, Data3, Data4 FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
INNER JOIN TABLE3 ON TABLE1.ID = TABLE3.ID
INNER JOIN TABLE4 ON TABLE1.ID = TABLE4.ID
WHERE TABLE.ID=' X '

How can I delete and update columns in this 4 tables by ID? Can somebody help me? I am beginner.

Answer 1

Update column values

UPDATE table1
SET data1 = myvalue
WHERE table1.id = ' X '

UPDATE table2
SET data2 = myvalue
WHERE table1.id = ' X '

UPDATE table3
SET data3 = myvalue
WHERE table1.id = ' X '

UPDATE table4
SET data4 = myvalue
WHERE table1.id = ' X '

Remove rows matching certain criteria

DELETE FROM table1
WHERE data1 = myvalue

DELETE FROM table2
WHERE data2 = myvalue

DELETE FROM table3
WHERE data3 = myvalue

DELETE FROM table4
WHERE data4 = myvalue

Remove column from table

ALTER TABLE table1
DROP COLUMN data1


ALTER TABLE table2
DROP COLUMN data2


ALTER TABLE table3
DROP COLUMN data3


ALTER TABLE table4
DROP COLUMN data4

Answer 2

In standard SQL you can SELECT from more than one table at a time, but you cannot DELETE or UPDATE more than one table at a time. Therefore you will use commands like:

UPDATE Table1 SET Column = NewData WHERE . . .

and

DELETE FROM Table1 WHERE . . .

The exception is if the tables are related to each other "officially", by using PRIMARY and FOREIGN KEYS, you can have the UPDATE of the linking columns and the DELETE of rows "flow" through the linked tables. To do this you use the CASCADE option of SQL when declaring the keys.

Some vendors offer further non-standard extensions that allow you to UPDATE or DELETE from more than one table, but for those you'll have to specify the database you're using.

Answer 3

I don't get why you are doing it that way. I think your design is wrong.

If you are going to update/delete something that is in several tables, add a new table and relate every table to this one.