Insert increment values into column SQL

Question

0.00/5 (No votes)

See more:

Hi Guys. I'm new to SQL. I have 2 table Vehicle and Cards.

Table: Vehicle
ID_Vehicles(Pk)
137
138
139
140

Table: Cards	 
ID_Cards(Pk)	VehiclesID(Fk)
338	          137
339	          138
340	          NULL
341	          NULL

I wanted to update the ID_vehicles(primary key) in vehicles table, to VehiclesID(Foreign key) in Cards table.

Such that

In cards table.
ID_cards 340 will link to VehiclesID 139

341 will link to VehiclesID 140

342 will link to VehiclesID 141 ...
and the link goes on until the all ID_cards linked to vehiclesID.

Should i use insert function to insert increment values into vehiclesID column in cards table?

Any kind soul willing to help a newbie to SQL here Pls advise.
If this is a wrong forum to ask. Please advise which website i can post to?[enter image description here][1]

What I have tried:

My Codes as as below:

SQL

DECLARE @IncrementValue int SET @IncrementValue = 139 UPDATE CARDS SET VehiclesID = @IncrementValue,@IncrementValue=@IncrementValue+1 where ID_CARDS between 340 and 537

However error message prompted below: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_CARDS_VEHICLESID" The conflict occurred in database "HECPOLL", table "dbo.VEHICLES", column 'ID_VEHICLES'

Posted 18-Jul-18 2:46am

js solution

Updated 16-Aug-18 0:38am

CHill60

v2

Add a Solution

Comments

Ziee-M 18-Jul-18 9:57am

You just have to set the id field as autoincrement in your table, the database will automaticaly increment the values when you insert new row.
Since its autoincrement, you dont have to pass the id.
here is w3 link : https://www.w3schools.com/sql/sql_autoincrement.asp

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mudgilsks · Answer 1 · 2018-07-18T04:30:00

Hi, In SQL foreign key allows only values in the primary key column of the reference table. In this case first you need to find the value in the primary table and then update it into the secondary table. For this you can use output inserted.column value into a temp table and then use it into secondary table as foreign key value. For more please check below link in which you can get value of inserted primary key and use it into secondary table.
The OUTPUT Clause for INSERT and DELETE Statements - SQLServerCentral[^]

Skipton Corp · Answer 2 · 2018-08-15T23:53:00

Solution 3

Hello

A solution from a newbie:

SQL

ALTER TABLE Cards
ADD FOREIGN KEY (ID_Vehicles) REFERENCES Vehicle (ID_Vehicles)

1. Alter table.
2. Add a new column (Foreign Key) that can create a link.
3. Reference the Foreign Key to the Primary Key column in the Vehicle table.

Posted 15-Aug-18 23:53pm

Skipton Corp

Santosh kumar Pithani · Answer 3 · 2018-08-16T00:39:00

SQL

--if want to update parent table row will reflect same in child than better to use "ON UPDATE CASCADE " with foreign key reference level..

CREATE TABLE Vehicle(ID_Vehicles int primary key)
insert into Vehicle values(137),(138),(139),(140);

CREATE TABLE Cards(ID_Cards  int primary key,VehiclesID  int foreign key(VehiclesID) references Vehicle(ID_Vehicles) ON UPDATE CASCADE)

INSERT INTO Cards values
(338,137 )
,(339,138 )
,(340,NULL)
,(341,NULL)

select VehiclesID from Cards where ID_Cards=339; --before updating parent table

update Vehicle set ID_Vehicles=136 where ID_Vehicles=338;

select VehiclesID from Cards where ID_Cards=339; --after updating parent table

Insert increment values into column SQL

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0