CODEWITHSUNDEEP
sqlsql-updatesql-insert
Jim
Jim

Reputation: 923

SQL - keep values with UPDATE statement

I have a table "news" with 10 rows and cols (uid, id, registered_users, ....) Now i have users that can log in to my website (every registered user has a user id). The user can subscribe to a news on my website.

In SQL that means: I need to select the table "news" and the row with the uid (from the news) and insert the user id (from the current user) to the column "registered_users". 

INSERT INTO news (registered_users)
VALUES (user_id)

The INSERT statement has NO WHERE clause so i need the UPDATE clause.

UPDATE news
SET registered_users=user_id
WHERE uid=post_news_uid

But if more than one users subscribe to the same news the old user id in "registered_users" is lost....

Is there a way to keep the current values after an sql UPDATE statement?

I use PHP (mysql). The goal is this:

Upvotes: 0

Views: 2342

Answers (3)

LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

You should create an additional table to map users to news they have registeres on like:

create table user_news (user_id int, news_id int);

that looks like

----------------
| News  | Users|
----------------
| 5     | 22   |
| 5     | 33   |
| 5     | 45   |
| 7     | 21   |
| ...   | ...  |
----------------

Then you can use multiple queries to first retrieve the news_id and the user_id and store them inside variables depending on what language you use and then insert them into the user_news.

The advantage is, that finding all users of a news is much faster, because you don't have to parse every single idstring "(22, 33, 45)"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

It sounds like you are asking to insert a new user, to change a row in news from:

 5     22,33

and then user 45 signs up, and you get:

 5    22,33,45

If I don't understand, let me know. The rest of this solution is an excoriation of this approach.

This is a bad, bad, bad way to store data. Relational databases are designed around tables that have rows and columns. Lists should be represented as multiple rows in a table, and not as string concatenated values. This is all the worse, when you have an integer id and the data structure has to convert the integer to a string.

The right way is to introduce a table, say NewsUsers, such as:

create table NewsUsers (
    NewsUserId int identity(1, 1) primary key,
    NewsId int not null,
    UserId int not null,
    CreatedAt datetime default getdaete(),
    CreatedBy varchar(255) default sysname
);

I showed this syntax using SQL Server. The column NewsUserId is an auto-incrementing primary key for this table. The columns NewsId is the news item (5 in your first example). The column UserId is the user id that signed up. The columns CreatedAt and CreatedBy are handy columns that I put in almost all my tables.

With this structure, you would handle your problem by doing:

insert into NewsUsers
    select 5, <userid>;

Upvotes: 2

Hart CO
Hart CO

Reputation: 34784

It sounds like you want to INSERT with a SELECT statement - INSERT with SELECT

Example:

 INSERT INTO tbl_temp2 (fld_id)
      SELECT tbl_temp1.fld_order_id
      FROM tbl_temp1 
      WHERE tbl_temp1.fld_order_id > 100;

Upvotes: 1

Related Questions